表结构： dao----->下放UserDao(数据库中user表的专门的增删改查，专门针对某一个表的增删改查) entity—>下放数据库中的创建类，数据库中有多少字段就private 字段 ，getset方法 ，序列化。 test----->测试增删改查操作 utils---->连接数据库
创建一张表进行增删改查的操作
DROP TABLE USER; CREATE TABLE USER( uid INT PRIMARY KEY AUTO_INCREMENT, userName VARCHAR(20) UNICODE, PASSWORD VARCHAR(64), STATUS INT DEFAULT 1, #1 在线 ，0离线 regtime TIMESTAMP DEFAULT NOW() # 默认为当前时间 ) INSERT INTO USER(username,PASSWORD) VALUES('admin','123456'); INSERT INTO USER(username,PASSWORD) VALUES('admin1','123456'); INSERT INTO USER(username,PASSWORD) VALUES('admin2','123456'); INSERT INTO USER(username,PASSWORD) VALUES('admin3','123456'); INSERT INTO USER(username,PASSWORD) VALUES('admin4','123456'); INSERT INTO USER (username,PASSWORD) VALUES("admin6","123456")dao包下UserDao类
import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.ArrayList; import java.util.Date; import java.util.List; import com.zhibang.entity.User; import com.zhibang.utils.DBUtils; /** * 对User实体类进行CURD * -- 实际就是对tb_user表进行CURD * @author Administrator * */ public class UserDao { private Connection conn = null; private PreparedStatement ps = null; private ResultSet rs = null; /** * 1. 增加操作 * @param user * 接收一个User对象，将对象添加到数据库表中 */ public int addUser(User user) { // 定义要执行的SQL语句 String sql = "INSERT INTO user(userName, `password`) VALUES('"+ user.getUserName() +"', MD5('"+ user.getPassword() +"'))"; // 设置 int n = 0; try { //通过DBUtis类中的getConnection方法获得与数据库的连接 conn = DBUtils.getConnection(); //预编译执行上面定义的SQL语句（不是真正执行） ps = conn.prepareStatement(sql); //预编译 //执行SQL n = ps.executeUpdate(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } finally { //关闭资源 DBUtils.close(conn, ps); } return n; } /** * 2. 删除操作 * -- 根据uid删除记录 */ public int delUserById(Integer uid) { // 定义要执行的SQL语句 String sql = "delete from tb_user where uid=" + uid; // 设置 int n = 0; try { //通过DBUtis类中的getConnection方法获得与数据库的连接 conn = DBUtils.getConnection(); //预编译执行上面定义的SQL语句（不是真正执行） ps = conn.prepareStatement(sql); //预编译 //执行SQL n = ps.executeUpdate(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } finally { //关闭资源 DBUtils.close(conn, ps); } return n; } /** * 3. 修改操作 * -- 根据uid修改密码或状态 */ public int updateUser(User user) { // 定义要执行的SQL语句 String sql = "update tb_user set `password`="+ user.getPassword() +", status="+ user.getStatus() +" where uid="+ user.getUid(); // 设置 int n = 0; try { //通过DBUtis类中的getConnection方法获得与数据库的连接 conn = DBUtils.getConnection(); //预编译执行上面定义的SQL语句（不是真正执行） ps = conn.prepareStatement(sql); //预编译 //执行SQL n = ps.executeUpdate(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } finally { //关闭资源 DBUtils.close(conn, ps); } return n; } /** * 4. 查询操作 * -- 查询表中所有记录 */ public List<User> getAllUser(){ String sql = "select * from user"; List<User> users = new ArrayList<User>(); try { //获得连接 conn = DBUtils.getConnection(); //执行sql ps = conn.prepareStatement(sql); rs = ps.executeQuery(); //获得查询出来的结果集 while(rs.next()) { int uid = rs.getInt("uid"); String userName = rs.getString("userName"); String password = rs.getString("password"); int status = rs.getInt("status"); Date regtime = rs.getDate("regtime"); //实例化一个User对象，并添加到List集合 users.add(new User(uid, userName, password, status, regtime)); } } catch (Exception e) { // TODO: handle exception e.printStackTrace(); } finally { DBUtils.close(conn, ps, rs); } return users; } }entity包下User类
import java.io.Serializable; import java.util.Date; /** * 用户表的实体类 tb_user * @author Administrator * */ public class User implements Serializable { private static final long serialVersionUID = 3365877867525443334L; private Integer uid; private String userName; private String password; private Integer status; private Date regtime; public User() { super(); } public User(String userName, String password, Integer status, Date regtime) { super(); this.userName = userName; this.password = password; this.status = status; this.regtime = regtime; } public User(Integer uid, String userName, String password, Integer status, Date regtime) { super(); this.uid = uid; this.userName = userName; this.password = password; this.status = status; this.regtime = regtime; } public Integer getUid() { return uid; } public void setUid(Integer uid) { this.uid = uid; } public String getUserName() { return userName; } public void setUserName(String userName) { this.userName = userName; } public String getPassword() { return password; } public void setPassword(String password) { this.password = password; } public Integer getStatus() { return status; } public void setStatus(Integer status) { this.status = status; } public Date getRegtime() { return regtime; } public void setRegtime(Date regtime) { this.regtime = regtime; } @Override public String toString() { return "User [uid=" + uid + ", userName=" + userName + ", password=" + password + ", status=" + status + ", regtime=" + regtime + "]"; } }Test包下Test类
import java.util.List; import com.zhibang.dao.UserDao; import com.zhibang.entity.User; public class Test { public static void main(String[] args) { // // User user = new User(); // user.setUserName("administrator"); // user.setPassword("123456"); // // int n = new UserDao().addUser(user); // if(n>0) { // System.out.println("添加成功"); // }else { // System.out.println("添加失败"); // } // // 查询所有记录 List<User> users = new UserDao().getAllUser(); for (User user : users) { System.out.println(user); } // // 修改uid为2的密码为'abcdef'，并设置状态为0 // User user = new User(); // user.setUid(2); // user.setPassword("abcdef"); // user.setStatus(0); // // int n = new UserDao().updateUser(user); // if(n>0) { // System.out.println("修改成功"); // }else { // System.out.println("修改失败"); // } // 删除uid为2的记录 // int n = new UserDao().delUserById(2); // if(n>0) { // System.out.println("删除成功"); // }else { // System.out.println("删除失败"); // } } }utils包下DBUtils类
import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; /** * 数据库工具类 * @author Administrator * */ public class DBUtils { /** * 0. 设置数据源的四要素：驱动、连接、用户名、密码 */ private static final String DRIVER="com.mysql.jdbc.Driver"; private static final String URL="jdbc:mysql://localhost:3306/girls";// 如果插入数据库文字为？号加上这行代码?useUnicode=true&characterEncoding=UTF-8 private static final String UNAME="root"; private static final String UPWD="123456"; /** * 1. 加载数据库驱动 */ static { try { Class.forName(DRIVER); } catch (ClassNotFoundException e) { // TODO Auto-generated catch block e.printStackTrace(); } } /** * 2. 获得数据库连接 */ public static Connection getConnection() throws SQLException{ return DriverManager.getConnection(URL, UNAME, UPWD); } /** * 3. 关闭数据库连接，释放资源 */ public static void close(Connection conn, PreparedStatement ps, ResultSet rs) { try { if(conn != null) { conn.close(); } if(ps != null) { ps.close(); } if(rs != null) { rs.close(); } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } public static void close(Connection conn, PreparedStatement ps) { close(conn, ps, null); } public static void close(Connection conn) { close(conn, null, null); } }复制粘贴即可跑通。 需要导入mysql-connector-java-5.1.21.jar 在百度上搜素即可获得下载地址，下载即可。 把mysql-connector-java-5.1.21.jar包复制粘贴到项目中，鼠标右键jar包----选中build path----》add build path;
