package db;

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

public class ConnMySQL {
    public static void connMySQL() throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.jdbc.Driver");
        try (
                Connection conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/think_blog","root","");
                Statement stmt = conn.createStatement();
                /*
                 * Statement有三种执行SQL的方法
                 * 1.execute()可执行任何SQL语句返回boolean
                 * 2.executeQuery()返回ResultSet
                 * 3.executeUpdate()执行DML语句返回受影响记录数
                 */
                ResultSet rs = stmt.executeQuery("select * from tp_article")) {
            //ResultSet通过next()能向前迭代，通过各种getXxx()方法获取对应字段值
            while(rs.next()) {
                System.out.println(rs.getInt(1)+"\t | "+rs.getInt(2) + "\t | "+rs.getInt(3) + "\t |"+rs.getString(4));
            }
        }
    }
    
    public static void main(String[] args) throws SQLException, ClassNotFoundException {
        connMySQL();
    }
}

package db;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

public class ExecuteDDL {
    private String driver;
    private String url;
    private String user;
    private String pass;
    public void initParam(String paramFile) throws FileNotFoundException, IOException, ClassNotFoundException {
        //用Properties类加载属性文件
        Properties prop = new Properties();
        prop.load(new FileInputStream(paramFile));
        driver = prop.getProperty("driver");
        url = prop.getProperty("url");
        user = prop.getProperty("user");
        pass = prop.getProperty("pass");
        Class.forName(driver);
    }
    public void createTable(String sql) throws SQLException, ClassNotFoundException {
        try (
                Connection conn = DriverManager.getConnection(url, user, pass);
                Statement stmt = conn.createStatement()) {
            //执行DDL语句，创建数据表
            stmt.executeUpdate(sql);
        }
        
    }
    public int insertData(String sql) throws SQLException, ClassNotFoundException {
        try (
                Connection conn = DriverManager.getConnection(url, user, pass);
                Statement stmt = conn.createStatement()) {
            //执行DML语句，插入数据
            return stmt.executeUpdate(sql);
        }
    }
    
    public void executeSQL(String sql) throws ClassNotFoundException, SQLException {
        try (
                Connection conn = DriverManager.getConnection(url, user, pass);
                Statement stmt = conn.createStatement()) {
            //execute()返回boolean,true表示有ResultSet, falseb表示没有
            boolean hasResultSet = stmt.execute(sql);
            if (hasResultSet) {
                try (
                        // 获取结果集
                        ResultSet rs = stmt.getResultSet()) {
                    //ResultSetMetaData是用于分析结果集的接口
                    ResultSetMetaData rsmd = rs.getMetaData();
                    int columnCount = rsmd.getColumnCount();
                    while(rs.next()) {
                        for(int i = 0; i < columnCount; i++) {
                            System.out.print(rs.getString(i + 1) + "\t");
                        }
                        System.out.print("\n");
                    }
                }
            } else {
                System.out.println("该SQL执行结果影响的记录条数有 "+ stmt.getUpdateCount() + " 条");
            }
        }
    }
    
    public void insertUseStatement() throws SQLException, ClassNotFoundException {
        long start = System.currentTimeMillis();
        try (
                Connection conn = DriverManager.getConnection(url, user, pass);
                Statement stmt = conn.createStatement()) {
            for (int i = 0 ; i < 1 ; i++) {
                stmt.executeUpdate("insert into jdbc_test values("
                        + " null ,‘title"+i+"‘,‘content"+i+"‘)");
            }
            System.out.println("使用Statement费时:"+(System.currentTimeMillis()-start));
        }
    }
    
    public void insertUsePrepare() throws ClassNotFoundException, SQLException {
        long start = System.currentTimeMillis();
        try (
                Connection conn = DriverManager.getConnection(url, user, pass);
                PreparedStatement pstmt = conn.prepareStatement("insert into jdbc_test values(null,?,?)")) {
            pstmt.setString(1, "title");
            pstmt.setString(2, "content");
            pstmt.executeUpdate();
            /*
            for (int i = 0 ; i < 1 ; i++) {
                pstmt.setString(1, "title"+i);
            }
            */
            System.out.println("使用PreparedStatement费时:"+(System.currentTimeMillis()-start));
        }
    }
    
    public static void main(String[] args) throws ClassNotFoundException, SQLException, FileNotFoundException, IOException {
        ExecuteDDL ed = new ExecuteDDL();
        ed.initParam("mysql.ini");
        /*
        ed.createTable("create table jdbc_test "
                + "(jdbc_id int auto_increment primary key, "
                + "jdbc_name varchar(255), "
                + "jdbc_desc text);");
        System.out.println("=================建表成功=================");
        */
        
        /*
        int result = ed.insertData("insert into jdbc_test (jdbc_name, jdbc_desc)"
                + "select art_title, art_content from tp_article;");
        System.out.println("====共有 "+result+" 条记录受影响===");
        */
        
        //ed.executeSQL("select * from jdbc_test");
        
        ed.insertUsePrepare();
    }
}