Java学习笔记--sql与java

1.JDBC
1.1JDBC定义：一套接口，用于连接数据库和Java
1.2使用JDBC发送sql语句的条件：
连接mysql数据库：
数据库主机
端口
数据库用户名
数据库密码
连接的数据库
1.3JPDC核心API
a.Driver接口： 数据库驱动程序的接口，所有具体数据库厂商需要的驱动程序需要实现此接口。
Connection connect(String url, Properties info) 用于获取数据库连接
b.Connection接口：与具体的数据库的连接对象。
Statement createStatement() 创建一个静态sql语句对象
PreparedStatement prepareStatement(String sql) 创建预编译的sql语句对象
CallableStatement prepareCall(String sql) 创建存储过程的sql语句对象
c.Statement接口：用于执行静态 SQL 语句
int executeUpdate(String sql) 执行更新操作的sql语句 （create/alter/drop) DDL语句
(insert/update/delete）DML语句
ResultSet executeQuery(String sql) 执行查询操作的sql语句
(select)（DQL查询语句）
d.PreparedStatement接口：用于执行预编译的 SQL 语句（是Statement的子接口）
int executeUpdate() 执行更新操作的sql语句
ResultSet executeQuery() 执行查询操作的sql语句
e. CallableStatement接口：用于执行 SQL 存储过程的接口（是PreparedStatement的子 接口）
ResultSet executeQuery() 执行存储过程的sql语句
f.ResultSet接口：结果集对象。 存储所有数据库查询的结果，用该对象进行数据遍历。
boolean next() ： 把光标移动到下一行。如果下一行有数据，返回true，如果没有下一行数 据，返回false。
getXXX（列索引|列字段名称）： 获取字段的数据

package com.jdbc.a_driver;

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

public class DriverDemo {
    private static String url = "jdbc:mysql://localhost:3306/day20";
    //jdbc协议:mysql协议://主机地址:端口号/需要连接的数据库名称
    private static String user = "root";
    private static String password="root";

    public static void main(String[] args) throws Exception {
        /**
         * java程序连接mysql数据库
         * 1.mysql数据库的主机地址
         * 2.端口号
         * 3.用户名
         * 5.密码
         * 6.需要连接的数据库
         * 
         * 需求：先使用java程序连接我们的数据库，需要一个连接对象Connection
         */
        //conn1();
        //conn2();
        conn3();
    }

    //我们以后就使用这种方式获取数据库的连接对象
    private static void conn3() throws Exception {
        //注册驱动，我们发现mysql驱动程序的Driver实现类已经帮我们在静态代码块中注册好了驱动，
        //我们在此时只需要将Driver实现类加载到我们的内存中，static代码块就会自动执行，我们的驱动也就自动注册了
        //注册驱动
        Class.forName("com.mysql.jdbc.Driver");

        //获取java连接数据库的对象
        Connection conn = DriverManager.getConnection(url, user, password);

        //打印这个连接对象
        System.out.println(conn);

    }


    private static void conn2() throws Exception {
        //使用驱动管理类，连管理我们的驱动程序，并获取连接
        //1.注册驱动
        //public static void registerDriver(Driver driver)throws SQLException
        Driver driver = new com.mysql.jdbc.Driver();
        DriverManager.registerDriver(driver);

        //获取连接3
        //public static Connection getConnection(String url,String user,String password)throws SQLException
        Connection conn = DriverManager.getConnection(url, user, password);

        //打印这里数据库连接
        System.out.println(conn);
    }


    //直连
    private static void conn1() throws SQLException {
        //创建一个驱动类对象Driver
        Driver driver = new com.mysql.jdbc.Driver();
        //获取java连接数据库的连接
        //Connection connect(String url, Properties info)throws SQLException
        //创建一个properties集合
        Properties prop = new Properties();
        prop.setProperty("user", user);
        prop.setProperty("password", password);
        Connection conn = driver.connect(url, prop);
        //打印这个连接对象，如果连接对象不为空，就说明我们已经成功获取到了连接对象
        System.out.println(conn);
    }

}

2.Statement对象执行SQL操作

public class Demo1 {
    //数据库的连接的URL
    private static String url = "jdbc:mysql://localhost:3306/day17";
    //数据库用户名
    private static String user = "root";
    //数据库密码
    private static String password = "root";

    public static void main(String[] args){
        Connection conn = null;
        Statement stmt = null;
        try {
            //1.驱动驱动程序
            Class.forName("com.mysql.jdbc.Driver");
            //2.从驱动程序管理类获取连接
            conn = DriverManager.getConnection(url, user, password);
            //3.通过Connection对象获取Statement对象
            stmt = conn.createStatement();
            //4.准备sql语句
            String sql = "CREATE TABLE student(id INT PRIMARY KEY AUTO_INCREMENT,NAME VARCHAR(20),gender VARCHAR(2))";
            //5.执行sql语句，返回结果
            int count = stmt.executeUpdate(sql);

            System.out.println("影响了"+count+"行");
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        } finally{
            //6.关闭资源（先关闭statement，再关闭connection）
            if(stmt!=null)
                try {
                    stmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                    throw new RuntimeException(e);
                }
            if(conn!=null)
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                    throw new RuntimeException(e);
                }
        }
    }
}

执行其他语句的操作与上述执行DDL语句操作大同小异
3.PreparedStatement对象执行SQL操作
PreparedStatement是一个接口，表示预编译的SQL语句，SQL 语句被预编译并存储在 PreparedStatement 对象中。然后可以使用此对象多次高效地执行该语句。

public class Demo1 {

    /**
     * 插入操作
     */
    @Test
    public void test1(){
        Connection conn = null;
        PreparedStatement stmt = null;
        try{
            //获取连接
            conn = JdbcUtil.getConnection();
            String sql = "INSERT INTO student(NAME,gender) VALUES(?,?)"; //预编译sql：使用？号代替参数值。一个？号代表一个参数值
            //创建PreparedStatement对象,执行预编译的sql语句
            stmt = conn.prepareStatement(sql);
            //设置参数
            /**
             * 参数一： 参数位置。从1开始
             * 参数二： 参数实际值
             * 注意： 所有参数必须要赋值
             */
            stmt.setString(1, "rose");
            stmt.setString(2, "女");
            //发送参数，执行sql语句
            int count = stmt.executeUpdate();
            System.out.println(count);
        }catch(Exception e){
            e.printStackTrace();
            throw new RuntimeException(e);
        }finally{
            //关闭资源
            JdbcUtil.close(conn, stmt, null);
        }
    }

Statement和PreparedStatement比较–>

一、语法结构不同
            1）Statment执行静态sql语句，且sql可以拼接。
            2）PreparedStatement可以先执行预编译的sql语句，在预编译sql语句中使用？进行参数占位，后面                    在进行参数赋值
    二、原理不同
            1）Statement不能进行sql缓存
            2）而PreparedStatement可以进行sql缓存，执行效率会比Statement快！！！

    三、安全性不同
            1）Statement存在sql注入的风险(使用登录注册讲解sql注入)
            2）而PreparedStatement可以有效防止用户注入。

4.CallableStatement对象执行存储过程
CallableStatement是用于执行SQL存储过程的接口。JDBC API 提供了一个存储过程 SQL 转义语法，该语法允许对所有 RDBMS 使用标准方式调用存储过程。此转义语法有一个包含结果参数的形式和一个不包含结果参数的形式。如果使用结果参数，则必须将其注册为 OUT 参数。其他参数可用于输入、输出或同时用于二者。参数是根据编号按顺序引用的，第一个参数的编号是 1。

public void test1(){
        Connection conn = null;
        CallableStatement stmt = null;
        ResultSet rs = null;
        try{
            //获取连接
            conn = JdbcUtil.getConnection();
            //创建CallableStatement对象
            String sql = "CALL pro_findById(?)";//预编译sql、可以带？号
            //执行预编译的sql
            stmt = conn.prepareCall(sql);
            //设置参数
            stmt.setInt(1, 4);
            //发送参数，执行sql,返回结果
            rs = stmt.executeQuery();// 注意： 执行存储过程必须使用exeuteQuery！！！！
            //遍历结果
            while(rs.next()){
                int id = rs.getInt("id");
                String name = rs.getString("name");
                String gender = rs.getString("gender");
                System.out.println(id+"\t"+name+"\t"+gender+"\t");
            }
        }catch(Exception e){
            e.printStackTrace();
            throw new RuntimeException(e);
        }finally{
            //关闭资源
            JdbcUtil.close(conn, stmt, rs);
        }
    }

5.优化JDBC工具类

private static String url = null;
    private static String user = null;
    private static String password = null;
    private static String className = null;

    static{
        //注册驱动，注册一次就可以了
        //注册驱动
        try {
            //给成员变量赋值，将文件中的键值对加载到properties集合中
            Properties prop = new Properties();
            InputStream in = new FileInputStream("db.properties");
            prop.load(in);
            url = prop.getProperty("url");
            user = prop.getProperty("user");
            password = prop.getProperty("password");
            className = prop.getProperty("className");
            System.out.println(url);
            System.out.println(user);
            System.out.println(password);
            System.out.println(className);
            Class.forName(className);
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }

    //获取连接
    public static Connection getConnection(){
        try {
            //获取连接
            Connection conn = DriverManager.getConnection(url, user, password);
            //返回conn
            return conn;
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException();
        }
    }

    //释放资源
    public static void close(ResultSet rs,Statement stmt,Connection conn){
        //先判空后释放
        if (rs!=null) {
            try {
                rs.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
                throw new RuntimeException();
            }
        }

        if (stmt!=null) {
            try {
                stmt.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
                throw new RuntimeException();
            }
        }

        if (conn!=null) {
            try {
                conn.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
                throw new RuntimeException();
            }
        }

    }