Day7.7.rar
javaweb.zip

Jdbc增删改查

  1. package com.xx.Jdbc.demo;
  3. import java.sql.DriverManager;
  4. import java.sql.ResultSet;
  5. import java.sql.SQLException;
  6. import java.sql.Statement;
  7. import java.util.Random;
  8. import java.util.Scanner;
  10. import org.junit.Test;
  12. import java.sql.Connection;
  15. public class Demo {
  16.     /**
  17.      *     查询
  18.      */
  19.     @Test
  20.     public void Demo1() throws SQLException{
  21.         Connection conn = null;
  22.         Statement stmt = null;
  23.         ResultSet rs = null;
  25.         try {
  26.             conn =  JunitDemo.getConnection();
  27.             stmt = conn.createStatement();
  28.             String sql = "select * from t_user";
  29.              rs = stmt.executeQuery(sql);
  30.              while (rs.next()) {
  31.                 System.out.println("id："+rs.getInt("uid")+"name："+rs.getString("name")+"password："+rs.getString("password"));
  32.             }
  34.         } catch (SQLException e) {
  35.             // TODO Auto-generated catch block
  36.             e.printStackTrace();
  37.         }finally {
  38.             JunitDemo.release(rs ,stmt, conn);
  39.         }
  41.     }
  43.     /**
  44.      *     更新
  45.      */
  46.     @Test
  47.     public void Demo2() throws SQLException{
  48.         Connection conn = null;
  49.         Statement stmt = null;
  51.         try {
  52.             conn =  JunitDemo.getConnection();
  53.             stmt = conn.createStatement();
  54.             String sql = "update t_user set password='xxxxxx' where uid=4";
  55.              int num = stmt.executeUpdate(sql);
  56.              if (num>0) {
  57.                  System.out.println("成功");
  58.             }else {
  59.                 System.out.println("失败");
  60.             }
  61.         } catch (SQLException e) {
  62.             // TODO Auto-generated catch block
  63.             e.printStackTrace();
  64.         }finally {
  65.             JunitDemo.release(stmt, conn);
  66.         }
  68.     }
  70.     /**
  71.      *     添加
  72.      */
  73.     @Test
  74.     public void Demo3() throws SQLException{
  75.         Connection conn = null;
  76.         Statement stmt = null;
  78.         try {
  79.             conn =  JunitDemo.getConnection();
  80.             stmt = conn.createStatement();
  81.             String sql = "insert into t_user (name,password) values  ('柒萧萧','qs12345')";
  82.              int num = stmt.executeUpdate(sql);
  83.              if (num>0) {
  84.                  System.out.println("成功");
  85.             }else {
  86.                 System.out.println("失败");
  87.             }
  88.         } catch (SQLException e) {
  89.             // TODO Auto-generated catch block
  90.             e.printStackTrace();
  91.         }finally {
  92.             JunitDemo.release(stmt, conn);
  93.         }
  95.     }
  97.     /**
  98.      *     删除
  99.      */
  100.     @Test
  101.     public void Demo4() throws SQLException{
  102.         Connection conn = null;
  103.         Statement stmt = null;
  105.         try {
  106.             conn =  JunitDemo.getConnection();
  107.             stmt = conn.createStatement();
  108.             String sql = "delete from t_user where uid = 4";
  109.              int num = stmt.executeUpdate(sql);
  110.              if (num>0) {
  111.                  System.out.println("成功");
  112.             }else {
  113.                 System.out.println("失败");
  114.             }
  115.         } catch (SQLException e) {
  116.             // TODO Auto-generated catch block
  117.             e.printStackTrace();
  118.         }finally {
  119.             JunitDemo.release(stmt, conn);
  120.         }
  122.     }
  125.     /**
  126.      *     登录
  127.      */
  128.     @Test
  129.     public void Demo5() throws SQLException{
  130.         Connection conn = null;
  131.         Statement stmt = null;
  132.         ResultSet rs = null;
  134.         try {
  135.             conn =  JunitDemo.getConnection();
  136.             stmt = conn.createStatement();
  137.             Scanner scanner = new Scanner(System.in);
  138.             System.out.println("用户名");
  139.             String user = scanner.next();
  140.             System.out.println("密码");
  141.             String password = scanner.next();
  142.             String sql = "select count(*) from t_user where name='"+user+"' and password='"+password+"'";
  143.              rs = stmt.executeQuery(sql);
  144.              while (rs.next()) {
  145.                 if (rs.getInt(1)>0) {
  146.                     System.out.println("成功");
  147.                 }else {
  148.                     System.out.println("失败");
  149.                     System.exit(0);
  150.                     return; 
  151.                 }
  152.             }
  154.         } catch (SQLException e) {
  155.             // TODO Auto-generated catch block
  156.             e.printStackTrace();
  157.         }finally {
  158.             JunitDemo.release(stmt, conn);
  159.         }
  161.     }
  162. }

PreparedStatement之增删改查

  1. /**
  2.      *     PreparedStatement登录
  3.      */
  4.     @Test
  5.     public void Demo6() throws SQLException{
  6.         Connection conn = null;
  7.         PreparedStatement pstmt = null;
  8.         ResultSet rs = null;
  10.         try {
  11.             conn =  JunitDemo.getConnection();
  13.             Scanner scanner = new Scanner(System.in);
  14.             System.out.print("用户名");
  15.             String user = scanner.next();
  16.             System.out.print("密码");
  17.             String password = scanner.next();
  18.             String sql = "select count(*) from t_user where name=? and password=?";
  19. //            System.out.println(sql);
  20.             pstmt = conn.prepareStatement(sql);
  21.             pstmt.setString(1, user);
  22.             pstmt.setString(2, password);
  23.              rs = pstmt.executeQuery();
  24.              while (rs.next()) {
  25.                 if (rs.getInt(1)>0) {
  26.                     System.out.println("成功");
  27.                 }else {
  28.                     System.out.println("失败");
  29.                     System.exit(0);
  30.                     return; 
  31.                 }
  32.             }
  34.         } catch (SQLException e) {
  35.             // TODO Auto-generated catch block
  36.             e.printStackTrace();
  37.         }finally {
  38.             JunitDemo.release(rs, pstmt, conn);
  39.         }
  41.     }
  43.     /**
  44.      *     PreparedStatement插入
  45.      */
  46.     @Test
  47.     public void Demo7() throws SQLException{
  48.         Connection conn = null;
  49.         PreparedStatement pstmt = null;
  50.         ResultSet rs = null;
  52.         try {
  53.             conn =  JunitDemo.getConnection();
  55.             Scanner scanner = new Scanner(System.in);
  56.             System.out.println("用户名");
  57.             String user = scanner.next();
  58.             System.out.println("密码");
  59.             String password = scanner.next();
  60.             String sql = "insert into t_user (name,password) values (?,?)";
  61.             System.out.println(sql);
  62.             pstmt = conn.prepareStatement(sql);
  63.             pstmt.setString(1, user);
  64.             pstmt.setString(2, password);
  65.              int num = pstmt.executeUpdate();
  67.             if (num>0) {
  68.                 System.out.println("成功");
  69.             }else {
  70.                 System.out.println("失败");
  71.                 System.exit(0);
  72.                 return; 
  73.             }
  76.         } catch (SQLException e) {
  77.             // TODO Auto-generated catch block
  78.             e.printStackTrace();
  79.         }finally {
  80.             JunitDemo.release(pstmt, conn);
  81.         }
  83.     }
  85.     /**
  86.      *     PreparedStatement删除
  87.      */
  88.     @Test
  89.     public void Demo8() throws SQLException{
  90.         Connection conn = null;
  91.         PreparedStatement pstmt = null;
  92.         ResultSet rs = null;
  94.         try {
  95.             conn =  JunitDemo.getConnection();
  97.             Scanner scanner = new Scanner(System.in);
  98.             System.out.println("删除ID");
  99.             int id = scanner.nextInt();
  100.             String sql = "delete from t_user where id=?";
  101. //            System.out.println(sql);
  102.             pstmt = conn.prepareStatement(sql);
  103.             pstmt.setInt(1, id);
  105.              int num = pstmt.executeUpdate();
  107.             if (num>0) {
  108.                 System.out.println("成功");
  109.             }else {
  110.                 System.out.println("失败");
  111.                 System.exit(0);
  112.                 return; 
  113.             }
  116.         } catch (SQLException e) {
  117.             // TODO Auto-generated catch block
  118.             e.printStackTrace();
  119.         }finally {
  120.             JunitDemo.release(pstmt, conn);
  121.         }
  123.     }
  126.     /**
  127.      *     PreparedStatement更新
  128.      */
  129.     @Test
  130.     public void Demo9() throws SQLException{
  131.         Connection conn = null;
  132.         PreparedStatement pstmt = null;
  133.         ResultSet rs = null;
  135.         try {
  136.             conn =  JunitDemo.getConnection();
  138.             Scanner scanner = new Scanner(System.in);
  139.             System.out.println("更新ID");
  140.             int id = scanner.nextInt();
  141.             System.out.println("用户名修改为");
  142.             String up_name = scanner.next();
  143.             String sql = "update t_user set name=? where id=?";
  144. //            System.out.println(sql);
  145.             pstmt = conn.prepareStatement(sql);
  146.             pstmt.setString(1, up_name);
  147.             pstmt.setInt(2, id);
  149.              int num = pstmt.executeUpdate();
  151.             if (num>0) {
  152.                 System.out.println("成功");
  153.             }else {
  154.                 System.out.println("失败");
  155.                 System.exit(0);
  156.                 return; 
  157.             }
  160.         } catch (SQLException e) {
  161.             // TODO Auto-generated catch block
  162.             e.printStackTrace();
  163.         }finally {
  164.             JunitDemo.release(pstmt, conn);
  165.         }
  167.     }

JdbcUtils工具类

  1. package com.xx.JdbcUtils.Demo;
  3. import java.sql.*;
  5. public class JdbcUtils {
  7.     /**
  8.      * 声明成员变量
  9.      * */
  10.     private static final String driverClassName;
  11.     private static final String url;
  12.     private static final String user;
  13.     private static final String password;
  15.     /**
  16.      * 成员变量赋值
  17.      * */
  18.     static {
  19.         driverClassName = "com.mysql.jdbc.Driver"; //mysql jar包
  20.         url = "jdbc:mysql://localhost:3306/stusys";//数据库链接
  21.         user = "root"; //数据库用户名
  22.         password = "root"; //数据库密码
  23.     }
  26.     /**
  27.      * 加载驱动
  28.      * */
  29.     public static void loadDriver() throws ClassNotFoundException {
  30.         Class.forName(driverClassName);
  31.     }
  32.     /**
  33.      * 获得链接
  34.      * */
  35.     public static Connection getConnection() throws ClassNotFoundException {
  36.         loadDriver();//加载驱动
  37.         Connection conn = null;
  38.         try {
  39.            conn = DriverManager.getConnection(url,user,password); //获得链接
  40.         } catch (SQLException e) {
  41.             e.printStackTrace();
  42.         }
  43.         return conn;
  44.     }
  45.     /**
  46.      *  释放资源 Statement Connection
  47.      **/
  48.     public static void  release(Statement stmt,Connection conn) throws SQLException {
  49.         stmt.close();
  50.         conn.close();
  51.     }
  52.     /**
  53.      * 释放资源 ResultSet Statement Connection
  54.      * */
  55.     public static void  release(ResultSet rs, Statement stmt, Connection conn) throws SQLException {
  56.         rs.close();
  57.         stmt.close();
  58.         conn.close();
  59.     }
  60. }

使用JdbcUtils工具类

  1. package day;
  3. import com.xx.JdbcUtils.Demo.JdbcUtils;
  4. import org.junit.jupiter.api.Test;
  6. import java.sql.Connection;
  7. import java.sql.PreparedStatement;
  8. import java.sql.ResultSet;
  9. import java.sql.SQLException;
  11. public class Demo {
  12.     //声明全局遍变量
  13.     Connection conn;
  14.     PreparedStatement pstmt;
  15.     ResultSet rs;
  17.     /**
  18.      * 查询
  19.      * */
  20.     @Test
  21.     public void demo() throws ClassNotFoundException, SQLException {
  22.         String sql = "select * from sort";//编写sql语句
  23.         conn = JdbcUtils.getConnection(); //获取链接 调取JdbcUtils工具类
  24.         pstmt = conn.prepareStatement(sql);//执行sql语句
  25.         rs = pstmt.executeQuery();
  26.         //遍历集合
  27.         while (rs.next()){
  28.             //输出集合内容 ID user
  29.             System.out.println(rs.getInt("id")+"++++"+rs.getString("user") );
  30.         }
  32.     }
  33. }

db.properties

  1. Properties properties = new Properties();
  2. properties.load(new FileInputStream("src/db.properties"));
  3. driverClassName = properties.getProperty("driverClassName");
  4. url = properties.getProperty("url");
  5. name = properties.getProperty("name");
  6. password = properties.getProperty("password");
  1. driverClassName=com.mysql.jdbc.Driver
  2. url=jdbc:mysql://localhost:3306/rubysiu
  3. name=root
  4. password=root

QueryRunner

  1. /**
  2.      * 获取多条记录
  3.      * @throws SQLException 
  4.      * */
  5.     @Test
  6.     public void getAll() throws SQLException {
  7.         QueryRunner qr = new QueryRunner();
  8.         String sql = "selcet * from Student";
  9.         List<Student> list = qr.query(JunitDemo.getConnection(),sql,new BeanListHandler<Student>(Student.class));
  10.         for (Student student : list) {
  11.             System.out.println(student.toString());
  12.         }
  13.     }
  15.     String sql = "selcet * from Student where name=?";
  1.     /**
  2.      * 获取一条记录
  3.      * @throws SQLException 
  4.      * */
  5.     @Test
  6.     public void getOne() throws SQLException {
  7.         Scanner scanner = new Scanner(System.in);
  8.         System.out.println("用户名");
  9.         String name = scanner.next();
  10.         QueryRunner qr = new QueryRunner();
  11.         String sql = "selcet * from Student where name=?";
  12.         Object[] params={name};
  13.         Student student = qr.query(JunitDemo.getConnection(), sql,new BeanHandler<Student>(Student.class),params);
  14.         System.out.println(student.toString());
  16.     }