NOTE:之前说过了增加和查询的代码，今天一并补上
资源下载此资源下载价格为1摩拉，请先
博主Qq：2807306273

sqlyog中写入代码新建数据库

 复制代码
  1. /*在MySQL中创建一个名称为chapter01的数据库，
  2.   然后在该数据库中创建一个users表，SQL语句如下所示*/
  3. CREATE DATABASE jdbc;
  4. USE jdbc;
  5. CREATE TABLE users(
  6.         id INT PRIMARY KEY AUTO_INCREMENT,
  7.         name VARCHAR(40),
  8.         password VARCHAR(40),
  9.         email VARCHAR(60),
  10.         birthday DATE 
  11. )CHARACTER SET utf8 COLLATE utf8_general_ci;
  12.  
  13. /*数据库和表创建成功后，再向users表中插入3条数据，SQL语句如下所示*/
  14. INSERT INTO users(NAME,PASSWORD,email,birthday) 
  15. VALUES('zs','123456','zs@sina.com','1980-12-04');
  16. INSERT INTO users(NAME,PASSWORD,email,birthday) 
  17. VALUES('lisi','123456','lisi@sina.com','1981-12-04');
  18. INSERT INTO users(NAME,PASSWORD,email,birthday) 
  19. VALUES('wangwu','123456','wangwu@sina.com','1979-12-04');
  20. /*为了查看数据是否添加成功，使用SELECT语句查询users表，SQL语句如下所示*/
 复制代码
  1. package Bean;
  2.  
  3. import java.util.Date;
  4.  
  5. public class User {
  6.         private int id;
  7.         private String name;
  8.         private String password;
  9.         private String email;
  10.         private Date birthday;
  11.         public int getId() {
  12.             return id;
  13.         }
  14.         public void setId(int id) {
  15.             this.id = id;
  16.         }
  17.  
  18.     public String getName() {
  19.         return name;
  20.     }
  21.  
  22.     public void setName(String name) {
  23.         this.name = name;
  24.     }
  25.  
  26.     public String getPassword() {
  27.             return password;
  28.         }
  29.         public void setPassword(String password) {
  30.             this.password = password;
  31.         }
  32.         public String getEmail() {
  33.             return email;
  34.         }
  35.         public void setEmail(String email) {
  36.             this.email = email;
  37.         }
  38.         public Date getBirthday() {
  39.             return birthday;
  40.         }
  41.         public void setBirthday(Date birthday) {
  42.             this.birthday = birthday;
  43.         }
  44. }
  45.  
 复制代码
  1. package Dao;
  2.  
  3. import Bean.User;
  4. import utils.C3P0Utils;
  5. import utils.JDBCUtils;
  6.  
  7. import java.sql.Connection;
  8. import java.sql.PreparedStatement;
  9. import java.sql.ResultSet;
  10. import java.sql.Statement;
  11. import java.text.SimpleDateFormat;
  12. import java.util.ArrayList;
  13. import java.util.List;
  14.  
  15. public class UserDao {
  16.     // 添加用户的操作
  17.     public boolean insert(User user) {
  18.         Connection conn = null;
  19.         PreparedStatement stmt = null;
  20.         ResultSet rs = null;
  21.         try {
  22.             // 获得数据的连接
  23.             conn = JDBCUtils.getConnection();
  24. //            conn = C3P0Utils.getConnection();
  25.  
  26.             // 发送SQL语句
  27.             SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
  28.             String birthday = sdf.format(user.getBirthday());
  29.             String sql = " INSERT INTO users(NAME,PASSWORD,email,birthday) VALUES(?,?,?,?)";
  30.             // 获得Statement对象
  31.             stmt = conn.prepareStatement(sql);
  32.             stmt.setString(1, user.getName());
  33.             stmt.setString(2, user.getPassword());
  34.             stmt.setString(3, user.getEmail());
  35.             stmt.setString(4, birthday);
  36.             int num = stmt.executeUpdate();
  37.             if (num > 0) {
  38.                 return true;
  39.             }
  40.             return false;
  41.         } catch (Exception e) {
  42.             e.printStackTrace();
  43.         } finally {
  44.             JDBCUtils.release(rs, stmt, conn);
  45.         }
  46.         return false;
  47.     }
  48.  
  49.     //查询所有，返回的是多条数据对应的bean对象
  50.     public List<User> getall() {
  51.         List<User> list = new ArrayList<>();//删除集合存储查询到的所有对象
  52.         Connection connection = null;
  53.         PreparedStatement statement = null;
  54.         ResultSet set = null;
  55.         try {
  56.             connection = JDBCUtils.getConnection();//连接数据库
  57.             String sql = "select * from users";//SQL语句
  58.             statement = connection.prepareStatement(sql);//获得preparedStatment对象
  59.             set = statement.executeQuery();//执行SQL语句
  60.             //操作结果集
  61.             while (set.next()) {
  62.                 //获得每条记录各个字段的值
  63.                 int id = set.getInt("id");
  64.                 String name = set.getString("name");
  65.                 String password = set.getString("password");
  66.                 String email = set.getString("email");
  67.                 String birthday = set.getString("birthday");
  68.                 //将各个字段的值封装进bean对象
  69.                 User user = new User();
  70.                 user.setId(id);
  71.                 user.setName(name);
  72.                 user.setPassword(password);
  73.                 user.setEmail(email);
  74.                 user.setBirthday(new SimpleDateFormat("yyyy-MM-dd").parse(birthday));//要把字符串birthday转换成Date
  75.                 //将user放入集合
  76.                 list.add(user);
  77.             }
  78.             return list;//将集合返回给调用者
  79.         } catch (Exception e) {
  80.             e.printStackTrace();
  81.         } finally {
  82.             JDBCUtils.release(set, statement, connection);
  83.         }
  84.         return null;//如果发生异常，返回null
  85.     }
  86.  
  87.     //查询单个用户-----ID
  88.     public User getByID(int id) {
  89.         Connection connection = null;
  90.         PreparedStatement statement = null;
  91.         ResultSet set = null;
  92.         try {
  93.             connection = JDBCUtils.getConnection();//连接数据库
  94.             String sql = "select * from users where id="+id;//SQL语句
  95.             statement = connection.prepareStatement(sql);//获得preparedStatment对象
  96.             set = statement.executeQuery();//执行SQL语句
  97.             while (set.next()) {
  98.                 //获取查询到的该条记录的各个字段的值
  99.                 int userid = set.getInt("id");
  100.                 String name = set.getString("name");
  101.                 String password = set.getString("password");
  102.                 String email = set.getString("email");
  103.                 String birthday = set.getString("birthday");
  104.                 //将各个字段的值封装进bean对象
  105.                 User user = new User();
  106.                 user.setId(userid);
  107.                 user.setName(name);
  108.                 user.setPassword(password);
  109.                 user.setEmail(email);
  110.                 user.setBirthday(new SimpleDateFormat("yyyy-MM-dd").parse(birthday));//要把字符串birthday转换成Date
  111.                 return user;//将用户返回给调用者
  112.             }
  113.         } catch (Exception e) {
  114.             e.printStackTrace();
  115.         } finally {
  116.             JDBCUtils.release(set, statement, connection);
  117.         }
  118.         return null;
  119.     }
  120.     //删除
  121.     public boolean delet(int id) {
  122.         Connection conn = null;
  123.         PreparedStatement stmt = null;
  124.         ResultSet rs = null;
  125.         try {
  126.             // 获得数据的连接
  127.             conn = JDBCUtils.getConnection();
  128.  
  129.             // 书写SQL语句
  130.             String sql="delete from users where id="+id;
  131.             //获得preparedstatment对象
  132.             stmt=conn.prepareStatement(sql);
  133.             //执行sql语句
  134.             int num = stmt.executeUpdate();
  135.             if (num > 0) {
  136.                 return true;
  137.             }
  138.         } catch (Exception e) {
  139.             e.printStackTrace();
  140.         } finally {
  141.             JDBCUtils.release(rs, stmt, conn);
  142.         }
  143.         return false;
  144.     }
  145.  
  146. //修改
  147.     public boolean update(User user){
  148.         Connection connection = null;
  149.         PreparedStatement statement = null;
  150.         try{
  151.             connection=JDBCUtils.getConnection();
  152.             String sql="update users set name=?,password=?,email=?,birthday=? where id=?";
  153.             statement=connection.prepareStatement(sql);
  154.             statement.setString(1,user.getName());
  155.             statement.setString(2,user.getPassword());
  156.             statement.setString(3,user.getEmail());
  157.             statement.setString(4,new SimpleDateFormat("yyyy-MM-dd").format(user.getBirthday()));
  158.             statement.setInt(5,user.getId());
  159.  
  160.             int num = statement.executeUpdate();
  161.             if (num > 0) {
  162.                 return true;
  163.             }
  164.             return false;
  165.         } catch (Exception e) {
  166.             e.printStackTrace();
  167.         } finally {
  168.             JDBCUtils.release(statement,connection);
  169.         }
  170.         return false;
  171.         }
  172.     }
 复制代码
  1. package utils;
  2.  
  3. import java.sql.*;
  4.  
  5. public class JDBCUtils{
  6.  
  7.     //获得数据库连接
  8. public static Connection getConnection() throws Exception{
  9.     Statement stmt=null;
  10.     ResultSet rs=null;
  11.     Connection conn=null;
  12.  
  13.             // 1. 注册数据库的驱动
  14.             Class.forName("com.mysql.jdbc.Driver");
  15.             // 2.通过DriverManager获取数据库连接
  16.             String url = "jdbc:mysql://localhost:3306/jdbc";
  17.             String username = "root";
  18.             String password = "root";
  19.             conn = DriverManager.getConnection(url, username,
  20.                     password);
  21.             return conn;
  22.         }
  23.  
  24.         //释放资源
  25.     public static void release(ResultSet rs,Statement statement,Connection conn){
  26.         if(rs!=null){
  27.             try{
  28.                 rs.close();
  29.             }catch(SQLException e){
  30.                 e.printStackTrace();
  31.             }
  32.             rs=null;
  33.         }
  34.         if(statement!=null){
  35.             try{
  36.                 statement.close();
  37.             }catch(SQLException e){
  38.                 e.printStackTrace();
  39.             }
  40.             statement=null;
  41.         }
  42.         if(conn!=null){
  43.             try{
  44.                 conn.close();
  45.             }catch(SQLException e){
  46.                 e.printStackTrace();
  47.             }
  48.             conn=null;
  49.         }
  50.  
  51.         }
  52.         public static void release(Statement statement,Connection connection){
  53.             if(statement!=null){
  54.                 try{
  55.                     statement.close();
  56.                 }catch(SQLException e){
  57.                     e.printStackTrace();
  58.                 }
  59.                 statement=null;
  60.             }
  61.             if(connection!=null){
  62.                 try{
  63.                     connection.close();
  64.                 }catch(SQLException e){
  65.                     e.printStackTrace();
  66.                 }
  67.                 connection=null;
  68.             }
  69.         }
  70. }
  71.