JDBC连接mysql增删改查整体代码

第一种比较low：用了statment，没有用preparedstatement。另外，插入时，不灵活，不能调用参数，但是如果直接给函数形参的话就会被SQL注入攻击，所以，最好在sql语句中使用？代表要引进的参数。

工具类（DBUtil类）：用来连接和关闭数据库

 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 }
View Code

 

测试类（Test类）：其中有增删改查的功能，在测试类中写下sql语句，进行测试

 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 }
View Code

 

第二种解决了第一种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();
                }
            }
        }

    }
}

 

posted @ 2018-03-08 18:42  彩电  阅读(417)  评论(0)  编辑  收藏  举报