JDBCDemo JDBCUtils

1、参考

MySQL基础
mysql-connector-java-5.1.40.zip

2、代码

public class JDBCDemo01 {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        //1.加载驱动
//        DriverManager.registerDriver(new com.mysql.jdbc.Driver());
        //推荐这种写法加载驱动
        Class.forName("com.mysql.jdbc.Driver");
        //2.用户信息和URL
        // useSSL=true可能会报错
        String url = "jdbc:mysql://localhost:3306/test01?useUnicode=true&characterEncoding=utf8&useSSL=false";
        String userName = "root";
        String passWord = "root";
        //3.连接成功，数据库对象 Connection代表数据库
        Connection connection = DriverManager.getConnection(url, userName, passWord);
        //4.执行SQl的对象 Statement 执行的sql对象
        Statement statement = connection.createStatement();
        //5.执行SQL的对象 去 执行SQL ，可能存在结果，查看返回的结果
        String sql = "SELECT * FROM users";
        //返回的结果集 结果集中封装了我们全部的查询的结果
        ResultSet resultSet = statement.executeQuery(sql);
        while (resultSet.next()) {
            System.out.println("id=" + resultSet.getObject("id"));
            System.out.println("name=" + resultSet.getObject("name"));
            System.out.println("password=" + resultSet.getObject("password"));
            System.out.println("email=" + resultSet.getObject("email"));
            System.out.println("birthday=" + resultSet.getObject("birthday"));
            System.out.println("===============================");
        }
        //6.释放连接
        resultSet.close();
        statement.close();
        connection.close();
    }
}

3、JDBCUtils

import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

public class JDBCUtils {
    private static String driver = null;
    private static String url = null;
    private static String username = null;
    private static String password = null;

    static {
        try {
            InputStream in = JDBCUtils.class.getClassLoader().getResourceAsStream("db.properties");
            Properties properties = new Properties();
            properties.load(in);

            driver = properties.getProperty("driver");
            url = properties.getProperty("url");
            username = properties.getProperty("username");
            password = properties.getProperty("password");

            //驱动只用加载一次
            Class.forName(driver);
        } catch (IOException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    /**
     * 获取连接
     */
    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(url, username, password);
    }

    /**
     * 释放资源
     */
    public static void release(Connection con, Statement st, ResultSet rs) {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (st != null) {
            try {
                st.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (con != null) {
            try {
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

db.properties

driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/test01?useUnicode=true&characterEncoding=utf8&useSSL=false
username=root
password=root

4、sql注入漏洞解决

public class TestInsert {
    public static void main(String[] args) {
        Connection con = null;
        PreparedStatement st = null;
        ResultSet rs = null;
        try {
            con = JDBCUtils.getConnection();
            //使用?占位符代替参数
            String sql = "INSERT INTO users(`id`,`name`,`password`,`email`,`birthday`) VALUES (?,?,?,?,?)";
            //预编译SQL，先写SQL，然后不执行
            st = con.prepareStatement(sql);
            //手动给参数赋值
            st.setInt(1, 5);
            st.setString(2, "钱七");
            st.setString(3, "123456");
            st.setString(4, "qianqi@sina.com");
            st.setDate(5, new java.sql.Date(new java.util.Date().getTime()));
            int num = st.executeUpdate();
            if (num > 0) {
                System.out.println("插入成功！");
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.release(con, st, rs);
        }

    }
}