第一种比较low:用了statment,没有用preparedstatement。另外,插入时,不灵活,不能调用参数,但是如果直接给函数形参的话就会被SQL注入攻击,所以,最好在sql语句中使用?代表要引进的参数。
工具类(DBUtil类):用来连接和关闭数据库
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
1 package JDBC; 2 3 /** 4 * Created by Administrator on 2018/3/8 0008. 5 */ 6 import java.sql.Connection; 7 import java.sql.DriverManager; 8 import java.sql.ResultSet; 9 import java.sql.SQLException; 10 import java.sql.Statement; 11 12 public class DBUtil { 13 public static final String url="jdbc:mysql://127.0.0.1/bz?useSSL=false"; 14 public static final String username="root"; 15 public static final String password="root"; 16 public static final String driver="com.mysql.jdbc.Driver"; 17 public static Connection DBcon(){ 18 Connection con=null; 19 try { 20 Class.forName(driver); 21 } catch (ClassNotFoundException e) { 22 // TODO Auto-generated catch block 23 e.printStackTrace(); 24 } 25 try { 26 con= DriverManager.getConnection(url,username,password); 27 } catch (SQLException e) { 28 // TODO Auto-generated catch block 29 e.printStackTrace(); 30 } 31 return con; 32 } 33 34 public static void close(ResultSet rs,Statement stat,Connection conn){ 35 36 try { 37 if(stat!=null) 38 stat.close(); 39 } catch (SQLException e) { 40 // TODO Auto-generated catch block 41 e.printStackTrace(); 42 } 43 try { 44 if(conn!=null) 45 conn.close(); 46 } catch (SQLException e) { 47 // TODO Auto-generated catch block 48 e.printStackTrace(); 49 } 50 try { 51 if(rs!=null) 52 rs.close(); 53 } catch (SQLException e) { 54 // TODO Auto-generated catch block 55 e.printStackTrace(); 56 } 57 } 58 }
测试类(Test类):其中有增删改查的功能,在测试类中写下sql语句,进行测试
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
1 package JDBC; 2 3 /** 4 * Created by Administrator on 2018/3/8 0008. 5 */ 6 import java.sql.Connection; 7 import java.sql.ResultSet; 8 import java.sql.SQLException; 9 import java.sql.Statement; 10 public class Test { 11 public static Connection conn=null; 12 public static Statement stat=null; 13 public static ResultSet rs=null; 14 public static void main(String[] args){ 15 String select="select * from father;"; 16 String insert="insert into father values(null,'POP');"; 17 String update="update father set f_name='haha' where f_name='POP';"; 18 String delete="delete from father where f_name='haha';"; 19 conn=DBUtil.DBcon(); 20 select(select); 21 insert(insert); 22 select(select); 23 update(update); 24 select(select); 25 delete(delete); 26 select(select); 27 DBUtil.close(rs,stat, conn); 28 } 29 public static void select(String quary){ 30 try { 31 stat=conn.createStatement(); 32 rs=stat.executeQuery(quary); 33 while(rs.next()){ 34 System.out.println(rs.getObject("fid")+"|"+rs.getObject("f_name")); 35 } 36 } catch (SQLException e) { 37 e.printStackTrace(); 38 } 39 } 40 public static void insert(String quary){ 41 try { 42 stat=conn.createStatement(); 43 int i=stat.executeUpdate(quary); 44 System.out.println("插入"+i+"行"); 45 } catch (SQLException e) { 46 e.printStackTrace(); 47 } 48 } 49 public static void delete(String quary){ 50 try { 51 stat=conn.createStatement(); 52 int i=stat.executeUpdate(quary); 53 System.out.println("删除了"+i+"行"); 54 } catch (SQLException e) { 55 e.printStackTrace(); 56 } 57 58 } 59 public static void update(String quary){ 60 try { 61 stat=conn.createStatement(); 62 int i=stat.executeUpdate(quary); 63 System.out.println("更改"+i+"行"); 64 } catch (SQLException e) { 65 e.printStackTrace(); 66 } 67 } 68 }
第二种解决了第一种low方法的问题,并且还新增了方法调用和过程调用:使用了CallableStatement类
package com.weikun.jdbc; import jdk.internal.org.objectweb.asm.Type; import org.junit.Test; import java.sql.*; /** * Created by Administrator on 2018/3/12 0012. */ public class C { @Test public void testFun(){ Connection conn = null; CallableStatement cs = null; try { Class.forName("com.mysql.jdbc.Driver"); conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/bz?useUnicode=true&charactorCoding=UTF-8&useSSL=false", "root", "root"); cs=conn.prepareCall("{?=call f_1(?)}"); cs.registerOutParameter(1, Type.DOUBLE); cs.setDouble(2,0.3); cs.execute(); System.out.println(cs.getObject(1)); }catch(Exception e){ e.printStackTrace(); } } @Test public void testProd(){ Connection conn = null; CallableStatement cs = null; try { Class.forName("com.mysql.jdbc.Driver"); conn= DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/bz?useUnicode=true&charactorCoding=UTF-8&useSSL=false", "root", "root"); cs=conn.prepareCall("call p_1(?,?,?,?);"); cs.setInt(1,20005); cs.setBoolean(2,true); cs.setDouble(3,0.2); cs.registerOutParameter(4, Types.FLOAT); cs.execute();//返回的是第一个返回参数是不是个结果集,是的话返回1,不是的话返回0 System.out.println(cs.getObject(4)); }catch (Exception e){ e.printStackTrace(); }finally { if(cs!=null){ try { cs.close(); } catch (SQLException e) { e.printStackTrace(); } } if(conn!=null){ try { conn.close(); } catch (SQLException e) { e.printStackTrace(); } } } } @Test public void add() { Connection connection = null; PreparedStatement ps = null; try { Class.forName("com.mysql.jdbc.Driver"); connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/bz?useUnicode=true&charactorCoding=UTF-8&useSSL=false", "root", "root"); ps=connection.prepareStatement("insert into e(v_name,v_salary) values(?,?)"); ps.setString(1,"jack"); ps.setDouble(2,3000); System.out.println(ps.executeUpdate()); }catch (Exception e){ e.printStackTrace(); }finally { if(ps!=null){ try { ps.close(); } catch (SQLException e) { e.printStackTrace(); } } if(connection!=null){ try { connection.close(); } catch (SQLException e) { e.printStackTrace(); } } } } @Test public void del(){ Connection connection = null; PreparedStatement ps = null; try { Class.forName("com.mysql.jdbc.Driver"); connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/bz?useUnicode=true&charactorCoding=UTF-8&useSSL=false", "root", "root"); ps=connection.prepareStatement("DELETE from e where v_id=?"); ps.setInt(1,2); System.out.println(ps.executeUpdate()); }catch (Exception e){ e.printStackTrace(); }finally { if(ps!=null){ try { ps.close(); } catch (SQLException e) { e.printStackTrace(); } } if(connection!=null){ try { connection.close(); } catch (SQLException e) { e.printStackTrace(); } } } } @Test public void update(){ Connection connection = null; PreparedStatement ps = null; try { Class.forName("com.mysql.jdbc.Driver"); connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/bz?useUnicode=true&charactorCoding=UTF-8&useSSL=false", "root", "root"); ps=connection.prepareStatement("UPDATE e SET v_name=? WHERE v_id=?"); ps.setString(1,"jack"); ps.setInt(2,1); System.out.println(ps.executeUpdate()); }catch (Exception e){ e.printStackTrace(); }finally { if(ps!=null){ try { ps.close(); } catch (SQLException e) { e.printStackTrace(); } } if(connection!=null){ try { connection.close(); } catch (SQLException e) { e.printStackTrace(); } } } } @Test public void quaryCon(){ Connection connection=null; PreparedStatement ps=null; ResultSet rs=null; try { Class.forName("com.mysql.jdbc.Driver"); connection=DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/bz?useUnicode=true&charactorCoding=UTF-8&useSSL=false","root","root"); ps=connection.prepareStatement("select * from products where prod_price>? and vend_id=?"); ps.setDouble(1,15.0); ps.setInt(2,1003); rs=ps.executeQuery(); while (rs.next()){ System.out.println(rs.getString("prod_name")); } } catch (Exception e) { e.printStackTrace(); }finally { if(rs!=null){ try { rs.close(); } catch (SQLException e) { e.printStackTrace(); } } if(ps!=null){ try { ps.close(); } catch (SQLException e) { e.printStackTrace(); } } if(connection!=null){ try { connection.close(); } catch (SQLException e) { e.printStackTrace(); } } } } @Test public void quaryAll(){ //查询所有结果集,打印其中的某几列 Connection connection=null; PreparedStatement ps=null; ResultSet rs=null; try { Class.forName("com.mysql.jdbc.Driver");//1、加载驱动 connection=DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/bz?useUnicode=true&charactorCoding=UTF-8&useSSL=false","root","root"); ps=connection.prepareStatement("select * from products");//尽量只用prepared态不使用Statment rs=ps.executeQuery();//返回一个结果集 while(rs.next()){ System.out.println(rs.getObject("prod_id"));//从结果集中取出列名为prod_id的数据。 } } catch (Exception e) { e.printStackTrace(); }finally { if(rs!=null){ try { rs.close(); } catch (SQLException e) { e.printStackTrace(); } } if(ps!=null){ try { ps.close(); } catch (SQLException e) { e.printStackTrace(); } } if(connection!=null){ try { connection.close(); } catch (SQLException e) { e.printStackTrace(); } } } } }