时间：2021-07-01 10:21:17 帮助过：3人阅读
2.静态sql查询
2.1建表
1 package gz.itcast.util; 2 3 import java.io.InputStream; 4 import java.sql.Connection; 5 import java.sql.DriverManager; 6 import java.sql.ResultSet; 7 import java.sql.SQLException; 8 import java.sql.Statement; 9 import java.util.Properties; 10 11 /** 12 * jdbc工具类 13 * @author APPle 14 * 15 */ 16 public class JdbcUtil { 17 private static String url = null; 18 private static String user = null; 19 private static String password = null; 20 private static String driverClass = null; 21 22 /** 23 * 静态代码块中（只加载一次） 24 */ 25 static{ 26 try { 27 //读取db.properties文件 28 Properties props = new Properties(); 29 /** 30 * . 代表java命令运行的目录 31 * 在java项目下，. java命令的运行目录从项目的根目录开始 32 * 在web项目下， . java命令的而运行目录从tomcat/bin目录开始 33 * 所以不能使用点. 34 */ 35 //FileInputStream in = new FileInputStream("./src/db.properties"); 36 37 /** 38 * 使用类路径的读取方式 39 * / : 斜杠表示classpath的根目录 40 * 在java项目下，classpath的根目录从bin目录开始 41 * 在web项目下，classpath的根目录从WEB-INF/classes目录开始 42 */ 43 InputStream in = JdbcUtil.class.getResourceAsStream("/db.properties"); 44 45 //加载文件 46 props.load(in); 47 //读取信息 48 url = props.getProperty("url"); 49 user = props.getProperty("user"); 50 password = props.getProperty("password"); 51 driverClass = props.getProperty("driverClass"); 52 53 54 //注册驱动程序 55 Class.forName(driverClass); 56 } catch (Exception e) { 57 e.printStackTrace(); 58 System.out.println("驱程程序注册出错"); 59 } 60 } 61 62 /** 63 * 抽取获取连接对象的方法 64 */ 65 public static Connection getConnection(){ 66 try { 67 Connection conn = DriverManager.getConnection(url, user, password); 68 return conn; 69 } catch (SQLException e) { 70 e.printStackTrace(); 71 throw new RuntimeException(e); 72 } 73 } 74 75 76 /** 77 * 释放资源的方法 78 */ 79 public static void close(Connection conn,Statement stmt){ 80 if(stmt!=null){ 81 try { 82 stmt.close(); 83 } catch (SQLException e) { 84 e.printStackTrace(); 85 throw new RuntimeException(e); 86 } 87 } 88 if(conn!=null){ 89 try { 90 conn.close(); 91 } catch (SQLException e) { 92 e.printStackTrace(); 93 throw new RuntimeException(e); 94 } 95 } 96 } 97 98 public static void close(Connection conn,Statement stmt,ResultSet rs){ 99 if(rs!=null) 100 try { 101 rs.close(); 102 } catch (SQLException e1) { 103 e1.printStackTrace(); 104 throw new RuntimeException(e1); 105 } 106 if(stmt!=null){ 107 try { 108 stmt.close(); 109 } catch (SQLException e) { 110 e.printStackTrace(); 111 throw new RuntimeException(e); 112 } 113 } 114 if(conn!=null){ 115 try { 116 conn.close(); 117 } catch (SQLException e) { 118 e.printStackTrace(); 119 throw new RuntimeException(e); 120 } 121 } 122 } 123 }
2.2插入数据
package gz.itcast.b_statement; import gz.itcast.util.JdbcUtil; import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; import java.sql.Statement; import org.junit.Test; /** * 使用Statement执行DML语句 * @author APPle * */ public class Demo2 { private String url = "jdbc:mysql://localhost:3306/day17"; private String user = "root"; private String password = "root"; /** * 增加 */ @Test public void testInsert(){ Connection conn = null; Statement stmt = null; try { //通过工具类获取连接对象 conn = JdbcUtil.getConnection(); //3.创建Statement对象 stmt = conn.createStatement(); //4.sql语句 String sql = "INSERT INTO student(NAME,gender) VALUES(‘李四‘,‘女‘)"; //5.执行sql int count = stmt.executeUpdate(sql); System.out.println("影响了"+count+"行"); } catch (Exception e) { e.printStackTrace(); throw new RuntimeException(e); } finally{ //关闭资源 /*if(stmt!=null) try { stmt.close(); } catch (SQLException e) { e.printStackTrace(); throw new RuntimeException(e); } if(conn!=null) try { conn.close(); } catch (SQLException e) { e.printStackTrace(); throw new RuntimeException(e); }*/ JdbcUtil.close(conn, stmt); } } /** * 修改 */ @Test public void testUpdate(){ Connection conn = null; Statement stmt = null; //模拟用户输入 String name = "陈六"; int id = 3; try { /*//1.注册驱动 Class.forName("com.mysql.jdbc.Driver"); //2.获取连接对象 conn = DriverManager.getConnection(url, user, password);*/ //通过工具类获取连接对象 conn = JdbcUtil.getConnection(); //3.创建Statement对象 stmt = conn.createStatement(); //4.sql语句 String sql = "UPDATE student SET NAME=‘"+name+"‘ WHERE id="+id+""; System.out.println(sql); //5.执行sql int count = stmt.executeUpdate(sql); System.out.println("影响了"+count+"行"); } catch (Exception e) { e.printStackTrace(); throw new RuntimeException(e); } finally{ //关闭资源 /*if(stmt!=null) try { stmt.close(); } catch (SQLException e) { e.printStackTrace(); throw new RuntimeException(e); } if(conn!=null) try { conn.close(); } catch (SQLException e) { e.printStackTrace(); throw new RuntimeException(e); }*/ JdbcUtil.close(conn, stmt); } } /** * 删除 */ @Test public void testDelete(){ Connection conn = null; Statement stmt = null; //模拟用户输入 int id = 3; try { /*//1.注册驱动 Class.forName("com.mysql.jdbc.Driver"); //2.获取连接对象 conn = DriverManager.getConnection(url, user, password);*/ //通过工具类获取连接对象 conn = JdbcUtil.getConnection(); //3.创建Statement对象 stmt = conn.createStatement(); //4.sql语句 String sql = "DELETE FROM student WHERE id="+id+""; System.out.println(sql); //5.执行sql int count = stmt.executeUpdate(sql); System.out.println("影响了"+count+"行"); } catch (Exception e) { e.printStackTrace(); throw new RuntimeException(e); } finally{ //关闭资源 /*if(stmt!=null) try { stmt.close(); } catch (SQLException e) { e.printStackTrace(); throw new RuntimeException(e); } if(conn!=null) try { conn.close(); } catch (SQLException e) { e.printStackTrace(); throw new RuntimeException(e); }*/ JdbcUtil.close(conn, stmt); } } }
2.3查询数据
package gz.itcast.b_statement; import gz.itcast.util.JdbcUtil; import java.sql.Connection; import java.sql.ResultSet; import java.sql.Statement; import org.junit.Test; /** * 使用Statement执行DQL语句（查询操作） * @author APPle * */ public class Demo3 { @Test public void test1(){ Connection conn = null; Statement stmt = null; try{ //获取连接 conn = JdbcUtil.getConnection(); //创建Statement stmt = conn.createStatement(); //准备sql String sql = "SELECT * FROM student"; //执行sql ResultSet rs = stmt.executeQuery(sql); //移动光标 /*boolean flag = rs.next(); flag = rs.next(); flag = rs.next(); if(flag){ //取出列值 //索引 int id = rs.getInt(1); String name = rs.getString(2); String gender = rs.getString(3); System.out.println(id+","+name+","+gender); //列名称 int id = rs.getInt("id"); String name = rs.getString("name"); String gender = rs.getString("gender"); System.out.println(id+","+name+","+gender); }*/ //遍历结果 while(rs.next()){ int id = rs.getInt("id"); String name = rs.getString("name"); String gender = rs.getString("gender"); System.out.println(id+","+name+","+gender); } }catch(Exception e){ e.printStackTrace(); throw new RuntimeException(e); }finally{ JdbcUtil.close(conn, stmt); } } }
3.动态SQL查询
3.1更新
package gz.itcast.c_prepared; import gz.itcast.util.JdbcUtil; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import org.junit.Test; /** * PreparedStatement執行sql語句 * @author APPle * */ public class Demo1 { /** * 增加 */ @Test public void testInsert() { Connection conn = null; PreparedStatement stmt = null; try { //1.获取连接 conn = JdbcUtil.getConnection(); //2.准备预编译的sql String sql = "INSERT INTO student(NAME,gender) VALUES(?,?)"; //?表示一个参数的占位符 //3.执行预编译sql语句(检查语法) stmt = conn.prepareStatement(sql); //4.设置参数值 /** * 参数一： 参数位置 从1开始 */ stmt.setString(1, "李四"); stmt.setString(2, "男"); //5.发送参数，执行sql int count = stmt.executeUpdate(); System.out.println("影响了"+count+"行"); } catch (Exception e) { e.printStackTrace(); throw new RuntimeException(e); } finally { JdbcUtil.close(conn, stmt); } } /** * 修改 */ @Test public void testUpdate() { Connection conn = null; PreparedStatement stmt = null; try { //1.获取连接 conn = JdbcUtil.getConnection(); //2.准备预编译的sql String sql = "UPDATE student SET NAME=? WHERE id=?"; //?表示一个参数的占位符 //3.执行预编译sql语句(检查语法) stmt = conn.prepareStatement(sql); //4.设置参数值 /** * 参数一： 参数位置 从1开始 */ stmt.setString(1, "王五"); stmt.setInt(2, 9); //5.发送参数，执行sql int count = stmt.executeUpdate(); System.out.println("影响了"+count+"行"); } catch (Exception e) { e.printStackTrace(); throw new RuntimeException(e); } finally { JdbcUtil.close(conn, stmt); } } /** * 删除 */ @Test public void testDelete() { Connection conn = null; PreparedStatement stmt = null; try { //1.获取连接 conn = JdbcUtil.getConnection(); //2.准备预编译的sql String sql = "DELETE FROM student WHERE id=?"; //?表示一个参数的占位符 //3.执行预编译sql语句(检查语法) stmt = conn.prepareStatement(sql); //4.设置参数值 /** * 参数一： 参数位置 从1开始 */ stmt.setInt(1, 9); //5.发送参数，执行sql int count = stmt.executeUpdate(); System.out.println("影响了"+count+"行"); } catch (Exception e) { e.printStackTrace(); throw new RuntimeException(e); } finally { JdbcUtil.close(conn, stmt); } } /** * 查询 */ @Test public void testQuery() { Connection conn = null; PreparedStatement stmt = null; ResultSet rs = null; try { //1.获取连接 conn = JdbcUtil.getConnection(); //2.准备预编译的sql String sql = "SELECT * FROM student"; //3.预编译 stmt = conn.prepareStatement(sql); //4.执行sql rs = stmt.executeQuery(); //5.遍历rs while(rs.next()){ int id = rs.getInt("id"); String name = rs.getString("name"); String gender = rs.getString("gender"); System.out.println(id+","+name+","+gender); } } catch (Exception e) { e.printStackTrace(); throw new RuntimeException(e); } finally { //关闭资源 JdbcUtil.close(conn,stmt,rs); } } }
3.2查询
package gz.itcast.c_prepared; import gz.itcast.util.JdbcUtil; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.Statement; import org.junit.Test; /** * 模拟用户登录效果 * @author APPle * */ public class Demo2 { //模拟用户输入 //private String name = "ericdfdfdfddfd‘ OR 1=1 -- "; private String name = "eric"; //private String password = "123456dfdfddfdf"; private String password = "123456"; /** * Statment存在sql被注入的风险 */ @Test public void testByStatement(){ Connection conn = null; Statement stmt = null; ResultSet rs = null; try { //获取连接 conn = JdbcUtil.getConnection(); //创建Statment stmt = conn.createStatement(); //准备sql String sql = "SELECT * FROM users WHERE NAME=‘"+name+"‘ AND PASSWORD=‘"+password+"‘"; //执行sql rs = stmt.executeQuery(sql); if(rs.next()){ //登录成功 System.out.println("登录成功"); }else{ System.out.println("登录失败"); } } catch (Exception e) { e.printStackTrace(); throw new RuntimeException(e); } finally { JdbcUtil.close(conn, stmt ,rs); } } /** * PreparedStatement可以有效地防止sql被注入 */ @Test public void testByPreparedStatement(){ Connection conn = null; PreparedStatement stmt = null; ResultSet rs = null; try { //获取连接 conn = JdbcUtil.getConnection(); String sql = "SELECT * FROM users WHERE NAME=? AND PASSWORD=?"; //预编译 stmt = conn.prepareStatement(sql); //设置参数 stmt.setString(1, name); stmt.setString(2, password); //执行sql rs = stmt.executeQuery(); if(rs.next()){ //登录成功 System.out.println("登录成功"); }else{ System.out.println("登录失败"); } } catch (Exception e) { e.printStackTrace(); throw new RuntimeException(e); } finally { JdbcUtil.close(conn, stmt ,rs); } } }
4.执行存储过程
package gz.itcast.d_callable; import gz.itcast.util.JdbcUtil; import