//URL
    private String url = "jdbc:mysql://localhost:3306/vmaxtam";
    //user
    private String user = "root";
    //password
    private String password = "root";

public void testDDL()throws Exception{
        //1.注冊驅(qū)動
        Class.forName("com.mysql.jdbc.Driver");
       
        //2.獲取連接
        Connection conn = DriverManager.getConnection(url, user, password);
       
        //3.創(chuàng)建Statement對象
        Statement stmt = conn.createStatement();
       
        //4.準(zhǔn)備sql語句
        String sql  ="CREATE TABLE student(sid INT PRIMARY KEY,sname VARCHAR(20),age INT)";
       
        //5.通過statement對象發(fā)送sql語句,返回執(zhí)行結(jié)果
        int count = stmt.executeUpdate(sql);
       
        //6.打印執(zhí)行結(jié)果
        System.out.println("影響了"+count+"條記錄");
}
//7.關(guān)閉資源
if(statement!=null)
{
    statement.close();
}

if(conn!=null)
{
    conn.close();
}

public class sqlUtil {
    private static String url = "jdbc:mysql://localhost:3306/vmaxtam";
    private static String user = "root";
    private static String password = "root";

    // 獲取連接
    public static Connection getconnection() {
        Connection conn = null;
        try {
            // 1.注冊驅(qū)動
            Class.forName("com.mysql.jdbc.Driver");
            // 2.獲取連接
            conn = DriverManager.getConnection(url, user, password);
            // 3.獲得statement對象
            Statement statement = conn.createStatement();
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return conn;
    }

    // 7.關(guān)閉資源
    public static void close(Statement statement, Connection connection) {
        {
            try {
                if (statement != null)
                    statement.close();
                if (connection != null) {
                    connection.close();
                }
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
    }
}

url=jdbc:mysql://localhost:3306/vmaxtam
user=root
password=root
driverClass=com.mysql.jdbc.Drive

public class sqlUtil {
    private static String url = null;
    private static String user = null;
    private static String password = null;
    private static String driverClass= null;

    static{
        try {
            //1.獲得字節(jié)碼對象
            Class clazz = sqlUtil.class;
           
            //2.調(diào)用getResourceAsStream獲取路徑
            InputStream inputStream = clazz.getResourceAsStream("/db.properties");
            Properties pro = new Properties();
            pro.load(inputStream);
           
            //3.讀取參數(shù)
            url=pro.getProperty("url");
            password=pro.getProperty("password");
            user=pro.getProperty("user");
            driverClass=pro.getProperty("driverClass");
           
            Class.forName(driverClass);
        } catch (Exception e) {
            e.printStackTrace();
            System.out.println("注冊失?。? + e.getMessage());
            throw new RuntimeException(e);
        }
    }
   
    // 獲取連接
    public static Connection getconnection() {
        Connection conn = null;
        try {       
            // 獲取連接
            conn = DriverManager.getConnection(url, user, password);
            // 獲得statement對象
            Statement statement = conn.createStatement();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return conn;
    }

    // 關(guān)閉資源
    public static void close(Statement statement, Connection connection) {
        {
            try {
                if (statement != null)
                    statement.close();
                if (connection != null) {
                    connection.close();
                }
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
    }
}

@Test
    public void testdsl() throws Exception {
        //獲取連接
        cnn2=sqlUtil.getconnection();
        Statement statement = cnn2.createStatement();
   
       
        //準(zhǔn)備SQL語句
        String sql = "select * from subject";
       
        //調(diào)用executeQuery執(zhí)行查詢語句
        ResultSet res = statement.executeQuery(sql);
       
        //查詢結(jié)束后res會指向表頭，想要獲取數(shù)據(jù)必須不斷地指向查詢結(jié)果的下一行，當(dāng)沒有下一行數(shù)據(jù)時，返回0.
        while(res.next())
        {
            //獲取查詢結(jié)果中字段為“sjid”的值，并且要明確類型
            int id = res.getInt("sjid");
           
            //獲取查詢結(jié)果中字段為“sjname”的值，并且要明確類型
            String name = res.getString("sjname");
            System.out.println("ID:" + id + "  NAME:" + name);
        }
        sqlUtil.close(statement, cnn2);
}

package com.vmaxtam.sqltest;

import java.sql.Connection;
import java.sql.PreparedStatement;

import org.junit.Test;

public class PreparedStatementTest {
    Connection connection = null;
    @Test
    public void ddldmlTest() throws Exception {
        // 1.獲取連接
        connection = sqlUtil.getconnection();

        // 2.準(zhǔn)備SQL語句,預(yù)編譯語句，參數(shù)用？號占位
        String sql = "INSERT INTO SUBJECT VALUES(?,?)";

        // 3.獲得對象
        PreparedStatement preparedStatement = connection.prepareStatement(sql);

        /*
         * 4.設(shè)置SQL參數(shù) 需要參數(shù)是第幾個，并且知道它的類型 下面第一句表示：SQL語句第一個參數(shù)是int類型，參數(shù)值設(shè)置為3，如此類推
         */
        preparedStatement.setInt(1, 3);
        preparedStatement.setString(2, "英語");

        // 5.交給數(shù)據(jù)庫執(zhí)行SQL
        int num = preparedStatement.executeUpdate();

        System.out.println("有" + num + "條記錄受到了影響");

              sqlUtil.close(preparedStatement , connection );
    }
}

@Test
    public void dqlTest() throws Exception {
        // 1.獲取連接
        connection = sqlUtil.getconnection();

        // 2.準(zhǔn)備SQL語句,預(yù)編譯語句，參數(shù)用？號占位
        String sql = "select * from subject where sjid=? or sjname=?";

        // 3.獲得對象
        PreparedStatement preparedStatement = connection.prepareStatement(sql);

        /*
         * 4.設(shè)置SQL參數(shù) 需要參數(shù)是第幾個，并且知道它的類型 下面第一句表示：SQL語句第一個參數(shù)是int類型，參數(shù)值設(shè)置為3，如此類推
         */
        preparedStatement.setInt(1, 2);
        preparedStatement.setString(2, "語文");

        // 5.交給數(shù)據(jù)庫執(zhí)行SQL
        ResultSet rst = preparedStatement.executeQuery();
       
        //6.迭代結(jié)果集
        while(rst.next())
        {
            int id = rst.getInt("sjid");
            String name = rst.getString("sjname");
            System.out.println("ID:" + id + "  NAME:" + name);
        }
       
        //7.關(guān)閉連接
        sqlUtil.close(preparedStatement, connection);
}

DELIMITER $
CREATE PROCEDURE pro_add(IN a INT , IN b VARCHAR(20),OUT c INT)
BEGIN   
    SELECT * FROM SUBJECT WHERE sjid=a OR sjname=b;
    SET c=a+a+a+a;
END $

@Test
public void calaST() throws Exception {
        //獲取連接
        connection= sqlUtil.getconnection();
        //準(zhǔn)備SQL語句
        String sql = "CALL pro_add(?,?,?)";
       
        //得到callableStatement對象
        CallableStatement cbs = connection.prepareCall(sql);
       
        //設(shè)置輸入?yún)?shù)，和preparedStatement一樣
        cbs.setInt(1, 3);
        cbs.setString(2, "數(shù)學(xué)");
       
        /*那么如何設(shè)置輸出參數(shù)呢？
         * 需要注冊輸出參數(shù)！
         */
        cbs.registerOutParameter(3, java.sql.Types.INTEGER);//需要使用內(nèi)置對象來設(shè)置參數(shù)類型
       
        //執(zhí)行SQL語句
        cbs.executeQuery();
       
        //利用getXXX方法得到相應(yīng)位置的輸出參數(shù)
        Integer num= cbs.getInt(3);
       
        System.out.println("a*4 is " + num);
       
             //關(guān)閉資源
        sqlUtil.close(cbs, connection);
}