JDBC

 

public class jdbc01 {
    public static void main(String[] args) throws IOException, ClassNotFoundException, SQLException {
        Properties properties = new Properties();
        properties.load(new FileInputStream("src\\mysql.properties"));
        String user = properties.getProperty("user");
        String pwd = properties.getProperty("password");
        String driver = properties.getProperty("driver");
        String url = properties.getProperty("url");
        Class.forName(driver);
        Connection connection = DriverManager.getConnection(url, user, pwd);
        System.out.println(connection);
    }
}

public class jdbc01 {
    public static void main(String[] args) throws IOException, ClassNotFoundException, SQLException {
        Properties properties = new Properties();
        properties.load(new FileInputStream("src\\mysql.properties"));
        String user = properties.getProperty("user");
        String pwd = properties.getProperty("password");
        String driver = properties.getProperty("driver");
        String url = properties.getProperty("url");
        Class.forName(driver);
        Connection connection = DriverManager.getConnection(url, user, pwd);
        System.out.println(connection);


        //得到Statement
        Statement statement = connection.createStatement();
        //sql语句
        String sql = "select id, name, sex from actor";
        //执行sql语句
        ResultSet resultSet = statement.executeQuery(sql);
        //使用while循环输出
        while (resultSet.next()){
            int id = resultSet.getInt(1);
            String name = resultSet.getString(2);
            String sex = resultSet.getString(3);
            System.out.println(id+"\t"+name+"\t"+sex);
        }
    }

SQL注入

 

 要防范SQL注入问题就要用PreparedStatement(从Statement扩展而来)

public class PreparedStatement {
    @SuppressWarnings({"all"})
    public static void main(String[] args) throws Exception{
        Properties properties = new Properties();
        properties.load(new FileInputStream("src\\mysql.properties"));
        String user = properties.getProperty("user");
        String pwd = properties.getProperty("password");
        String driver = properties.getProperty("driver");
        String url = properties.getProperty("url");
        Class.forName(driver);
        Connection connection = DriverManager.getConnection(url, user, pwd);
        //测试连接是否成功
        System.out.println(connection);
        //程序中用户输入的密码
        int pwd1 = 0;
        String name = "";
        //sql语句
        String  sql = "select name .pwd from admin where name = ? and pwd = ?";
        //实现preparedStatement接口
        java.sql.PreparedStatement preparedStatement = connection.prepareStatement(sql);
        //赋值
        preparedStatement.setString(1,name);
        preparedStatement.setString(2,pwd);
        //执行
        ResultSet resultSet = preparedStatement.executeQuery();
        if (resultSet.next()){
            System.out.println("登录成功");
        }else {
            System.out.println("登录失败");
        }
    }
}

JDBCUtils工具类

public class JDBCUtils {
    @SuppressWarnings({"all"})
    //定义相关属性
    private static String user;//用户名称
    private static String password;
    private static String url;
    private static String driver;

    static {

        Properties properties = new Properties();
        try {
            properties.load(new FileInputStream("src\\mysql.propertise"));
            user = properties.getProperty("user");
            password = properties.getProperty("password");
            url = properties.getProperty("url");
            driver = properties.getProperty("driver");
        } catch (IOException e) {
            //1.编译异常转成运行异常
            //2.这是调用者可以选择捕获改异常，也可以选择默认处理改异常，比较方便
            throw new RuntimeException(e);
        }
    }

    //连接数据库
    public static Connection getConnection() {
        try {
            return DriverManager.getConnection(url, user, password);
        } catch (SQLException e) {
            //1.编译异常转成运行异常
            //2.这是调用者可以选择捕获改异常，也可以选择默认处理改异常，比较方便
            throw new RuntimeException();
        }
    }
    //关闭资源
    /*
        1.ResultSet结果集
        2.Statement or PrepareStatement
        3.Connection
    */
    public static void close(ResultSet set, Statement statement, Connection connection){
        if (set != null){
            try {
                set.close();
        if (statement != null){
            statement.close();
        }
        if (connection != null){
            connection.close();
        }
            } catch (SQLException e) {
                throw new RuntimeException();
            }
        }
    }
}

public class jdbcUtils_use {
    @Test
    public  void testDML(){
        //得到连接
        PreparedStatement preparedStatement = null;
        Connection connection =null;
        //组织sql
        String sql = "update actor set name = ? where id = ?";
        //创建PrepareStatement对象
        try {
            connection = JDBCUtils.getConnection();
             preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,"mqs");
            preparedStatement.setInt(2, 4);
            //执行
            preparedStatement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            //关闭资源
            JDBCUtils.close(null,preparedStatement,connection);
        }
    }
}

事务处理：在多条sql语句执行时就可以回滚到保存点

public class JDBCtransaction {
    public static void main(String[] args) {
        Connection connection =null;
        String sql = "update account set balance = balance-100 where id=1";
        String sql2 = "update account set balance = balance+100 where id=2";
        PreparedStatement preparedStatement = null;
        try {
            //将connection设置为不自动提交
            connection.setAutoCommit(false);
            connection= JDBCUtils.getConnection();
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.executeQuery();
            int i=1/0;
            preparedStatement= connection.prepareStatement(sql2);
            preparedStatement.executeQuery();
        } catch (SQLException e) {
            try {
                connection.rollback();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            e.printStackTrace();
        } finally {
            JDBCUtils.close(null,preparedStatement,connection);
        }
    }
}

 

public class batch {
    public static void main(String[] args) {

    }
    public void batch() throws Exception {
        Connection connection = JDBCUtils.getConnection();
        String sql = "insert into admi2 values(null,?,?";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        System.out.println("开始执行");
        for (int i=0;i<5000;i++){
            preparedStatement.setString(1,"mqs"+i);
            preparedStatement.setString(2,"666");
            //将sql语批量加入
            preparedStatement.addBatch();
            //批量执行
            if (i+1%1000==0) {
                preparedStatement.executeBatch();
                preparedStatement.clearBatch();
            }
        }
        JDBCUtils.close(null,preparedStatement,connection);
    }
}

 

 德鲁伊连接池：

在连接池中关闭不是正真的关闭连接数据库，而是关闭连接池中的连接

public class Druid {
    public static void main(String[] args) throws Exception {
        Properties properties = new Properties();
        properties.load(new FileInputStream("src\\druid.properties"));
        //创建一个指定差数的数据连接池
        DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);
        long start = System.currentTimeMillis();
        for (int i=0; i<100000;i++){
            Connection connection = dataSource.getConnection();
            //System.out.println("连接成功");
            connection.close();
        }
        long end =System.currentTimeMillis();
        System.out.println(end-start);
    }
}

 

 集合封装，单行封装

 

 

 单列封装，DML操作