jdbc连接数据库演示(Statement、PrepareStatement，CallableStatement的演示)

在实际的开发中可能频繁用到数据库，这就要频繁创建连接对象以及频繁填充参数，一个好的方法是（类似实际框架中运用的方法）将参数以key-value的形式在src目录下(因为以后可能涉及到网络操作，方便获取)创建一个配置文件，将创建连接器的那部分代码做成一个工具类。

示例如下：
jdbc.properties的source视图(#表示注释)：

jdbc.properties的properties视图：

工具类代码：

import java.sql.Connection;
import java.sql.DriverManager;
import java.util.Properties;

public class ConnFactory {
    private static Connection con = null;

    private ConnFactory(){
    }
    static{
        try {
            Properties p = new Properties();        p.load(ConnFactory.class.getClassLoader().getResourceAsStream("jdbc.properties"));
            String driver = p.getProperty("driver");
            String url = p.getProperty("url");
            String user = p.getProperty("username");
            String password = p.getProperty("password");
            Class.forName(driver);
            con = DriverManager.getConnection(url, user, password);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    public static Connection getConnection(){
        return con;
    }
    public static void main(String[] args) {
        System.out.println(getConnection());
    }
}

好了，前置工作做好了，正式进入主题演示：

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import org.junit.Test;

import cn.hncu.util.ConnFactory;

public class JdbcDemo {
    @Test   //测试executeQuery()  只能用于查询
    public void testExecuteQuery(){
        Connection con = ConnFactory.getConnection();
        try {
            Statement st = con.createStatement();
            String sql = "select * from stud";
            ResultSet rs = st.executeQuery(sql);
            while(rs.next()){
                String no = rs.getString(1);
                String name = rs.getString(2);
                Integer age = rs.getInt(3);
                String address = rs.getString(4);
                System.out.println(no+","+name+","+age+","+address);
            }
            con.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    @Test   //测试executeUpdate()     可用于增删改
    public void testExecuteUpdate(){
        Connection con = ConnFactory.getConnection();
        try {
            Statement st = con.createStatement();
            //String sql = "insert into stud values('1009','小王',24,'杭州')";
            //String sql = "delete from stud where sno='1009'";
            String sql = "update stud set sname='王大拿' where sno='1007'";
            st.executeUpdate(sql);
            con.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    @Test   //测试    execute()   //可用于增删改查
    public void testExecute(){
        Connection con = ConnFactory.getConnection();
        try {
            Statement st = con.createStatement();
            //String sql = "insert into stud values('1010','四毛',24,'武汉')";
            //String sql = "update stud set saddress='广州' where sno='1003'";
            //String sql = "delete from stud where sno='1007'";
            String sql = "select * from stud";
            boolean boo = st.execute(sql);
            if(boo){
                ResultSet rs = st.getResultSet();
                while(rs.next()){
                    String no = rs.getString(1);
                    String name = rs.getString(2);
                    Integer age = rs.getInt(3);
                    String address = rs.getString(4);
                    System.out.println(no+"-"+name+"-"+age+"-"+address);
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

注:Java中如需要获取数据库中类型为datatime的数据，要分两次获取，分别为：

String datetime = st.getDate(m)+st.gettime(m);//其中m为数据所在字段的序号

在实际应用中，使用sql语句操作数据库，如果sql语句由程序内部制定，用Statement没什么问题，但如果sql语句的构造涉及到采用用户的输入信息，由于输入的未知性，就涉及到安全性bug了，这是必须用PrepareStatement代替Statement
演示如下：

@Test //容易产生bug：如输入name值为: aa,b'c
    public void reg() throws Exception{ 
        Statement st = ConnFactory.getConn().createStatement();
        Scanner sc = new Scanner(System.in);
        String id = sc.nextLine();
        String name = sc.nextLine();
        int age = Integer.parseInt(sc.nextLine());
        //String sql = "insert into stud values('P2001','kobe',25) ";
        String sql = "insert into stud values('"+id+"','"+name+"',"+age+") ";
        System.out.println(sql);
        st.execute(sql);
        ConnFactory.getConn().close();
    }

    @Test //容易被黑：如输入name值为: a' or '1'='1
    public void login() throws Exception{ 
        Statement st = ConnFactory.getConn().createStatement();
        Scanner sc = new Scanner(System.in);
        String id = sc.nextLine();
        String name = sc.nextLine();
        //String sql = "select count(*) from stud where id='P2001' and name='kobe'  ";
        String sql = "select count(*) from stud where id='"+id+"' and name='"+name+"'  ";
        System.out.println(sql);
        ResultSet rs = st.executeQuery(sql);
        rs.next();
        int n = rs.getInt(1);
        if(n<=0){
            System.out.println("登录失败...");
        }else{
            System.out.println("登录成功....");
        }

        ConnFactory.getConn().close();
    }

    //////综上：如果sql语句由程序内部直接指定，那么用Statement没问题。////


    //采用PrepareStatement
    @Test //不会被黑：如输入name值为: a' or '1'='1
    public void login2() throws Exception{
        Connection con = ConnFactory.getConn();
        Scanner sc = new Scanner(System.in);
        String id = sc.nextLine();
        String name = sc.nextLine();

        //创建预处理语句对象
        String sql = "select count(*) from stud where id=? and name=?  ";//凡是用户输入的地方，用“?”号(称占位符)填入
        PreparedStatement pst = con.prepareStatement(sql);
        //给占位设置值---设置参数
        pst.setString(1, id); //给第1个参数设置
        pst.setString(2, name); //给第2个参数设置

        ResultSet rs = pst.executeQuery();//这里不能传参数sql

        rs.next();
        int n = rs.getInt(1);
        if(n<=0){
            System.out.println("登录失败...");
        }else{
            System.out.println("登录成功....");
        }

        con.close();
    }

    @Test//演示批处理函数
    public void testExecuteBatch() throws Exception{
        Connection con = ConnFactory.getConnection();
        Statement st = con.createStatement();
        String sql = "insert into stud values('1011','五毛',25,'成都')";
        st.addBatch(sql);
        sql = "insert into stud values('1012','六毛',26,'重庆')";
        st.addBatch(sql);
        sql = "insert into stud values('1013','七毛',26,'深圳')";
        st.addBatch(sql);
        int[] sum = st.executeBatch();
        for(int x:sum){
            System.out.println(x);
        }
        con.close();
    }

        @Test//演示批处理函数
    public void testExecuteBatch2() throws Exception{
        Connection con = ConnFactory.getConnection();
        String sql = "insert into stud values(?,?,?,?)";
        PreparedStatement pst = con.prepareStatement(sql);
        pst.setString(1, "1014");
        pst.setString(2, "小粒子");
        pst.setInt(3, 27);
        pst.setString(4, "天津");
        pst.addBatch();
        int[] sum = pst.executeBatch();
        for(int x:sum){
            System.out.println(x);
        }
    }

注：批处理函数本身不带事务功能。如果某一个语句挂了，这个语句及它之后的语句都不能执行成功。

Java演示执行数据库的执行过程：
数据库执行过程代码：

DELIMITER $$

USE `mydata`$$

DROP PROCEDURE IF EXISTS `p4`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `p4`(IN m INT,OUT num INT)
BEGIN 
    SELECT * FROM stud WHERE age > m;
    SELECT COUNT(*) INTO num FROM stud;
END$$

DELIMITER ;

Java代码：

@Test
    public void testProcess() throws Exception{
        Connection con = ConnFactory.getConnection();
        String sql = "call p4(?,?)";
        CallableStatement cst = con.prepareCall(sql);
        cst.setInt(1, 25);
        cst.registerOutParameter(2, Types.INTEGER);
        ResultSet rs = cst.executeQuery();
        while(rs.next()){
            System.out.println(rs.getString(1)+","+rs.getString(2)+","+rs.getInt(3)+","+rs.getString(4));
        }
        int num = cst.getInt(2);
        System.out.println(num);
    }