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

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

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

package JDBC;

/**
 * Created by Administrator on 2018/3/8 0008.
 */
        import java.sql.Connection;
        import java.sql.DriverManager;
        import java.sql.ResultSet;
        import java.sql.SQLException;
        import java.sql.Statement;

public class DBUtil {
    public static final String url="jdbc:mysql://127.0.0.1/bz?useSSL=false";
    public static final String username="root";
    public static final String password="root";
    public static final String driver="com.mysql.jdbc.Driver";
    public static Connection DBcon(){
        Connection con=null;
        try {
            Class.forName(driver);
        } catch (ClassNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        try {
            con= DriverManager.getConnection(url,username,password);
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return con;
    }

    public static void close(ResultSet rs,Statement stat,Connection conn){

        try {
            if(stat!=null)
            stat.close();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        try {
            if(conn!=null)
            conn.close();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        try {
            if(rs!=null)
            rs.close();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }
}

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

package JDBC;

/**
 * Created by Administrator on 2018/3/8 0008.
 */
        import java.sql.Connection;
        import java.sql.ResultSet;
        import java.sql.SQLException;
        import java.sql.Statement;
public class Test {
    public static Connection conn=null;
    public static Statement stat=null;
    public static ResultSet rs=null;
    public static void main(String[] args){
        String select="select * from father;";
        String insert="insert into father values(null,'POP');";
        String update="update father set f_name='haha' where f_name='POP';";
        String delete="delete from father where f_name='haha';";
        conn=DBUtil.DBcon();
        select(select);
        insert(insert);
        select(select);
        update(update);
        select(select);
        delete(delete);
        select(select);
        DBUtil.close(rs,stat, conn);
    }
    public static void select(String quary){
        try {
            stat=conn.createStatement();
            rs=stat.executeQuery(quary);
            while(rs.next()){
                System.out.println(rs.getObject("fid")+"|"+rs.getObject("f_name"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    public static void insert(String quary){
        try {
            stat=conn.createStatement();
            int i=stat.executeUpdate(quary);
            System.out.println("插入"+i+"行");
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    public static void delete(String quary){
        try {
            stat=conn.createStatement();
            int i=stat.executeUpdate(quary);
            System.out.println("删除了"+i+"行");
        } catch (SQLException e) {
            e.printStackTrace();
        }

    }
    public static void update(String quary){
        try {
            stat=conn.createStatement();
            int i=stat.executeUpdate(quary);
            System.out.println("更改"+i+"行");
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

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

    }
}