功能：通过MVC三层架构，实现增、删除、改和查；
环境：JDK1.6，Mysql 5.5.48
表结构（表名：imooc_goddess）：
1、工具类：连接数据库
package com.imooc.db;import java.sql.DriverManager;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Connection;import java.sql.Statement;/** * 数据库连接工具类 */public class DBUtil { private static final String URL = "jdbc:mysql://192.168.12.118:3306/imooc"; private static final String USER = "root"; private static final String PASSWORD = "root"; private static Connection conn = null; static { try { // 1.加载驱动程序 Class.forName("com.mysql.jdbc.Driver"); // 2.获得数据库连接 conn = DriverManager.getConnection(URL, USER, PASSWORD); } catch (ClassNotFoundException e) { e.printStackTrace(); } catch (SQLException e) { e.printStackTrace(); } } // 获得连接 public static Connection getConnection() { return conn; } public static void main(String[] args) throws ClassNotFoundException, SQLException { // 3.操作数据库：增删改查 Statement stat = conn.createStatement(); ResultSet rsSet = stat.executeQuery("SELECT user_name,age FROM imooc_goddess;"); while (rsSet.next()) { System.out.println("姓名：" + rsSet.getString("user_name") + "，年龄：" + rsSet.getInt("age")); } }} 2、模型层，定义女神类，属性与表结构一致；
package com.imooc.model;import java.util.Date;public class Goddess { private Integer id; private String user_name; private Integer sex; private Integer age; private Date birthday; private String email; private String mobile; private String create_user; private Date create_date; private String update_user; private Date update_date; private Integer isdel; public Integer getId() { return id; } public void setId(Integer id) { this.id = id; } public String getUser_name() { return user_name; } public void setUser_name(String user_name) { this.user_name = user_name; } public Integer getSex() { return sex; } public void setSex(Integer sex) { this.sex = sex; } public Integer getAge() { return age; } public void setAge(Integer age) { this.age = age; } public Date getBirthday() { return birthday; } public void setBirthday(Date birthday) { this.birthday = birthday; } public String getEmail() { return email; } public void setEmail(String email) { this.email = email; } public String getMobile() { return mobile; } public void setMobile(String mobile) { this.mobile = mobile; } public String getCreate_user() { return create_user; } public void setCreate_user(String create_user) { this.create_user = create_user; } public Date getCreate_date() { return create_date; } public void setCreate_date(Date create_date) { this.create_date = create_date; } public String getUpdate_user() { return update_user; } public void setUpdate_user(String update_user) { this.update_user = update_user; } public Date getUpdate_date() { return update_date; } public void setUpdate_date(Date update_date) { this.update_date = update_date; } public Integer getIsdel() { return isdel; } public void setIsdel(Integer isdel) { this.isdel = isdel; } @Override public String toString() { return "Goddess [id=" + id + ", user_name=" + user_name + ", sex=" + sex + ", age=" + age + ", birthday=" + birthday + ", email=" + email + ", mobile=" + mobile + ", create_user=" + create_user + ", create_date=" + create_date + ", update_user=" + update_user + ", update_date=" + update_date + ", isdel=" + isdel + "]"; }} 3、屌丝类，主要操作数据库
package com.imooc.loser;import java.sql.Connection;import java.sql.Date;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;import java.util.ArrayList;import java.util.List;import java.util.Map;import com.imooc.db.DBUtil;import com.imooc.model.Goddess;/** * 模型层 屌丝Loser * * @author Administrator * */public class Loser { /** * 新增女神资料 * * @param goddess * @throws SQLException */ public void addGoddess(Goddess goddess) throws SQLException { // 获得连接 Connection conn = DBUtil.getConnection(); String sql = "INSERT INTO imooc_goddess(user_name,sex,age,birthday,email,mobile,create_user" + ",create_date,update_user,update_date,isdel) VALUES(?,?,?,?,?, ?,?,current_date(),?,current_date(), ?)"; // 预声明SQL语句，并检查 PreparedStatement preparedStatement = conn.prepareStatement(sql); // 参数值设置 preparedStatement.setString(1, goddess.getUser_name()); preparedStatement.setInt(2, 1); preparedStatement.setInt(3, goddess.getAge()); preparedStatement.setDate(4, new Date(goddess.getBirthday().getTime())); preparedStatement.setString(5, goddess.getEmail()); preparedStatement.setString(6, goddess.getMobile()); preparedStatement.setString(7, goddess.getCreate_user()); preparedStatement.setString(8, goddess.getUpdate_user()); preparedStatement.setInt(9, 0); // 执行SQL语句 preparedStatement.execute(); } /** * 更新女神资料 * * @param goddess */ public void updateGoddess(Goddess goddess) { // 获得连接 Connection conn = DBUtil.getConnection(); // SQL String sql = " UPDATE imooc_goddess " + " SET " + " user_name = ?, " + " age = ?, " + " birthday = ?, " + " email = ?, " + " mobile = ?, " + " update_user = ?, " + " update_date =current_date() " + " WHERE id = ? "; try { // 预声明SQL语句，并检查 PreparedStatement preparedStatement = conn.prepareStatement(sql); // 参数值设置 preparedStatement.setString(1, goddess.getUser_name()); preparedStatement.setInt(2, goddess.getAge()); preparedStatement.setDate(3, new Date(goddess.getBirthday() .getTime())); preparedStatement.setString(4, goddess.getEmail()); preparedStatement.setString(5, goddess.getMobile()); preparedStatement.setString(6, goddess.getUpdate_user()); preparedStatement.setInt(7, goddess.getId()); // 执行SQL语句 preparedStatement.execute(); } catch (SQLException e) { e.printStackTrace(); } } /** * 删除女神资料 * * @param id */ public void delGoddess(Integer id) { // 获得连接 Connection conn = DBUtil.getConnection(); String sql = " DELETE FROM imooc_goddess " + " WHERE id = ? "; try { // 预声明SQL语句，并检查 PreparedStatement preparedStatement = conn.prepareStatement(sql); // 参数值设置 preparedStatement.setInt(1, id); // 执行SQL语句 preparedStatement.execute(); } catch (SQLException e) { e.printStackTrace(); } } /** * 获得全部女神资料(完全自定义查询条件) * * @return */ public List<Goddess> query(List<Map<String, Object>> params) { // 创建List对象 List<Goddess> list = new ArrayList<Goddess>(); // 获得连接 Connection conn = DBUtil.getConnection(); try { // 创建StringBuilder对象 StringBuilder sb = new StringBuilder(); // SQL String sql = "SELECT *FROM imooc_goddess WHERE 1=1 "; // 添加字符串 sb.append(sql); // 参数非空，即WHERE有条件 if (params != null && params.size() > 0) { for (int i = 0; i < params.size(); i++) { Map<String, Object> map = params.get(i); // connection连接谓词 // name 字段名 // relation 关系词 // value 字段值 sb.append(map.get("connection") + " " + map.get("name") + " " + map.get("relation") + " " + map.get("value") + " "); } } // 创建Statement对象 PreparedStatement st = conn.prepareStatement(sb.toString()); // 创建女神Goddess对象 Goddess goddess = null; // 获得查询结果集 ResultSet set = st.executeQuery(); // 循环存储结果 while (set.next()) { // 创建一个对象 goddess = new Goddess(); // 设置女神对象goddess的属性 goddess.setId(set.getInt("id")); goddess.setUser_name(set.getString("user_name")); goddess.setSex(set.getInt("sex")); goddess.setAge(set.getInt("age")); goddess.setBirthday(set.getDate("birthday")); goddess.setEmail(set.getString("email")); goddess.setMobile(set.getString("mobile")); goddess.setCreate_user(set.getString("create_user")); goddess.setCreate_date(set.getDate("create_date")); goddess.setUpdate_user(set.getString("update_user")); goddess.setUpdate_date(set.getDate("update_date")); goddess.setIsdel(set.getInt("isdel")); // 列表添加女神对象 list.add(goddess); // 清空女神对象 goddess = null; } } catch (SQLException e) { e.printStackTrace(); } return list; } /** * 获得全部女神资料 * * @return */ public List<Goddess> query() { // 创建List对象 List<Goddess> list = new ArrayList<Goddess>(); // 获得连接 Connection conn = DBUtil.getConnection(); try { // 创建Statement对象 Statement st = conn.createStatement(); // 创建女神Goddess对象 Goddess goddess = null; // 获得查询结果集 ResultSet set = st .executeQuery("SELECT id,user_name,sex,age,birthday,email,mobile,create_user," + "create_date,update_user,update_date,isdel FROM imooc_goddess ORDER BY id"); // 循环存储结果 while (set.next()) { // 创建一个对象 goddess = new Goddess(); // 设置女神对象goddess的属性 goddess.setId(set.getInt("id")); goddess.setUser_name(set.getString("user_name")); goddess.setSex(set.getInt("sex")); goddess.setAge(set.getInt("age")); goddess.setBirthday(set.getDate("birthday")); goddess.setEmail(set.getString("email")); goddess.setMobile(set.getString("mobile")); goddess.setCreate_user(set.getString("create_user")); goddess.setCreate_date(set.getDate("create_date")); goddess.setUpdate_user(set.getString("update_user")); goddess.setUpdate_date(set.getDate("update_date")); goddess.setIsdel(set.getInt("isdel")); // 列表添加女神对象 list.add(goddess); // 清空女神对象 goddess = null; } } catch (SQLException e) { e.printStackTrace(); } return list; } /** * 通过姓名获得女神资料 * * @return */ public List<Goddess> query(String name) { // 创建List对象 List<Goddess> list = new ArrayList<Goddess>(); // 获得连接 Connection conn = DBUtil.getConnection(); try { // 创建StringBuilder对象 StringBuilder sb = new StringBuilder( "SELECT id,user_name,sex,age,birthday,email,mobile,create_user," + "create_date,update_user,update_date,isdel FROM imooc_goddess "); // 添加WHERE条件 sb.append("WHERE user_name=?"); // 创建PreparedStatement对象 PreparedStatement ps = conn.prepareStatement(sb.toString()); // 设置参数 ps.setString(1, name); // 获得查询结果集 ResultSet set = ps.executeQuery(); // 创建女神Goddess对象 Goddess goddess = null; // 循环存储结果 while (set.next()) { // 创建一个对象 goddess = new Goddess(); // 设置女神对象goddess的属性 goddess.setId(set.getInt("id")); goddess.setUser_name(set.getString("user_name")); goddess.setSex(set.getInt("sex")); goddess.setAge(set.getInt("age")); goddess.setBirthday(set.getDate("birthday")); goddess.setEmail(set.getString("email")); goddess.setMobile(set.getString("mobile")); goddess.setCreate_user(set.getString("create_user")); goddess.setCreate_date(set.getDate("create_date")); goddess.setUpdate_user(set.getString("update_user")); goddess.setUpdate_date(set.getDate("update_date")); goddess.setIsdel(set.getInt("isdel")); // 列表添加女神对象 list.add(goddess); // 清空女神对象 goddess = null; } } catch (SQLException e) { e.printStackTrace(); } return list; } /** * 通过ID获得女神资料 * * @param id * @return */ public Goddess get(Integer id) { // 获得连接 Connection conn = DBUtil.getConnection(); // 创建女神Goddess对象 Goddess goddess = null; try { // SQL String sql = "SELECT id,user_name,sex,age,birthday,email,mobile,create_user," + "create_date,update_user,update_date,isdel FROM imooc_goddess WHERE id=? "; // 创建Statement对象 PreparedStatement ps = conn.prepareStatement(sql); // 设置参数 ps.setInt(1, id); // 获得查询结果集 ResultSet set = ps.executeQuery(); while (set.next()) { // 创建一个对象 goddess = new Goddess(); // 设置女神对象goddess的属性 goddess.setId(set.getInt("id")); goddess.setUser_name(set.getString("user_name")); goddess.setSex(set.getInt("sex")); goddess.setAge(set.getInt("age")); goddess.setBirthday(set.getDate("birthday"));// 类java.sql.Date是类java.util.Date的子类 goddess.setEmail(set.getString("email")); goddess.setMobile(set.getString("mobile")); goddess.setCreate_user(set.getString("create_user")); goddess.setCreate_date(set.getDate("create_date")); goddess.setUpdate_user(set.getString("update_user")); goddess.setUpdate_date(set.getDate("update_date")); goddess.setIsdel(set.getInt("isdel")); } } catch (SQLException e) { e.printStackTrace(); } return goddess; }}