java-jdbc-all

jdbc相关解析

JDBC(Java DataBase Connectivity,Java数据库连接)是一种用于执行SQL语句的Java API,可以为多种关系数据库提供统一访问,它由一组用Java语言编写的类和接口组成,JDBC提供一种基准,据此可以构建更高级的工具和接口,使数据库开发人员能编写数据库应用程序。
原理简介

SUN提供一套访问数据库的规范(就是一组接口),并提供连接数据库的协议标准,然后各个数据库厂商会遵循SUN的规范提供一套访问自己公司的数据库服务器的API出现。SUN提供的规范命名为JDBC,而各个厂商提供的,遵循了JDBC规范的,可以访问自己数据库的API被称之为驱动!

 

 

 因此各个厂商做的驱动就是对于sun公司jdbcAPI的接口的实现类,所以必须要有驱动才能链接到数据库

 

JDBC的流程 :

111驱动器的加载

222数据库的连接

333创建执行对象

444执行sQL语句

555关闭资源

 

JDBC核心接口简介
DriverManagerConnectionStatementResultSet

 

111DriverManager类

驱动管理器,是管理一组JDBC驱动程序的基本服务,主要是用于 注册驱动 和 获取连接。

注册驱动:让JDBC接口知道连接的是哪个驱动(也就是连接哪个数据库)

Class.forName("com.mysql.jdbc.Driver");     //Mysql
Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");

Class.forName作用

因为这个主要是将类加载到内存当中

使用这个方法时,Driver类的静态代码块会向内存注册入戏

 

 

 

 

 

 

获取连接对象

通过DriverManager的api来获取连接对象。

Connection DriverManager.getConnection(url,username,password);
参数的含义:
url:用于标识数据库的位置,要连接的是什么数据库。
url格式:协议:子协议:子协议名称:IP地址:端口号:数据库名称(前部分是数据库厂商规定的)
username:是我们要连接的数据库的登陆名
password:使我们要登陆用户的对应密码(如果没设置可以为空)

常用URL地址的写法:

MySql:jdbc:mysql://localhost:3306/数据库名称
Oracle:jdbc:oracle:thin:@localhost:1521/数据库名称
SqlServer:jdbc:microsoft:sqlserver://localhost:1433/数据库名称

 

 

2222、Connection 接口:

如果可以获取到Connection对象,那么说明已经与数据库连接上了,Connection对象表示连接,与数据库的通讯录都是通过这个对象展开的:

Connection最重要的一个方法就是用来获取Statement对象和PreparedStatement对象;

创建Statement -语句执行者

Statement st = null;
//获取用于向数据库发送sql语句的statement
st = conn.createStatement();

 

 

创建一个预编译的语句执行对象:

PreperedStatement st = null;
String sql = "select * from users where username=? and password=?";
//获取用于向数据库发送sql语句的Preperedstatement
st = conn.preparedStatement(sql);//在此次传入,进行预编译

 

创建一个 CallableStatement 对象来调用数据库存储过程

CallableStatement prepareCall(String sql);

 

 

Statement接口:

Statement对象是SQL语句的执行者,是用来向数据库发送SQL语句的。

执行查询语句,返回一个集合:ResultSet executeQuery(String sql)
执行更新 插入 删除语句,返回影响行数int executeUpdate(String sql)
把多条sql语句放到一个批处理中:addBatch(String sql),然后通过executeBatch()向数据库发送一批sql语句执行。
执行给定的 SQL 语句,该语句可能返回多个结果:boolean execute(sql)

boolean execute使用注意:

注:该方法返回值不同,使用方式也不同:返回值为true时,表示执行的查询语句,使用getResultSet方法获取结果;返回值为false时,表示执行更新或DDL语句,使用getUpdateCount获取结果。

 

ResultSet接口:

ResultSet用于代表Sql语句的执行结果。Resultset封装执行结果时,采用的类似于表格的方式,ResultSet 对象维护了一个指针,初始的时候,指针指向的是结果集的第一行,可以通过提供的api对指针进行操作,查看结果集。

 

ResultSet对象对指针操作的方法:

next():移动到下一行
Previous():移动到前一行
absolute(int row):移动到指定行
beforeFirst():移动resultSet的最前面
afterLast() :移动到resultSet的最后面

 

直接获取值的方法:

获取任意类型的数据:

getObject(int index)
getObject(string columnName)
获取指定类型的数据,例如:

getString(int index)
getString(String columnName)

 

 

 

 statement 和PreperedStatement的区别

 关系:PreparedStatement继承自Statement,都是接口
 区别:PreparedStatement可以使用占位符,是预编译的,批处理比Statement效率高

 SQL 语句被预编译并存储在 PreparedStatement 对象中。然后可以使用此对象多次高效地执行该语句。

继承关系
作为 Statement 的子类,PreparedStatement 继承了 Statement 的所有功能,
Statement对象能做的操作Preparedstatement都能做,Preparedstatement能做的Statement不一定能做

安全性:

SQL注入:

String sql = "select * from tb_name where name= '"+varname+"' and passwd='"+varpasswd+"'";

我们把[' or '1' = '1]作为varpasswd传入进来.就会变成:
select * from tb_name = '随意' and passwd = '' or '1' = '1';
因为'1'='1'肯定成立,所以可以任何通过验证.更有甚者


再或者:把[';drop table tb_name;]作为varpasswd传入进来

得到:
select * from tb_name = '随意' and passwd = '';drop table tb_name;

而因为
PreparedStatement是实现占位符填充的

String param = "'test' or 1=1";
String sql = "select file from file where name = ?";
PreparedStatement preparedStatement = connection.prepareStatement(sql);
preparedStatement.setString(1, param);


得到的SQL语句是:
select file from file where name = '\'test\' or 1=1'

sql的执行函数已经确定,所以不会再破坏sql的结构。所以可以防止sql注入。

 

 

PreparedStatement具备预编译功能,其首先对sql语句进行预编译,然后再将值填充到对应占位符处

主要用的步骤格式如下;

perstmt = con.prepareStatement("insert into tb_name (col1,col2,col2,col4) values (?,?,?,?)");
perstmt.setString(1,var1);
perstmt.setString(2,var2);
perstmt.setString(3,var3);
perstmt.setString(4,var4);
perstmt.executeUpdate(); //prestmt是 PreparedStatement 对象实例

 

 整体的思路:

111因为每次都需要 建立连接,我们可以将连接作为一个累,同一接口就行:

222进行增删改查

 

注意:这里使用的是maven项目,因为比较好管理依赖包:

新建maven项目:加入依赖包:

  <!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
    <dependencies>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.49</version>
        </dependency>
<!--数据库连接池-->
        <!-- https://mvnrepository.com/artifact/org.apache.commons/commons-dbcp2 -->
        <dependency>
            <groupId>org.apache.commons</groupId>
            <artifactId>commons-dbcp2</artifactId>
            <version>2.2.0</version>
        </dependency>

 

建立获取连接的累:

import java.io.*;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;

public class JdbcConnection {
    /*
    关于数据库连接所需要的东西我们可以在这里直接赋值,也可以使用高级的做法,写配置文件jdbc.properties
    private static  final String driver = "com.mysql.jdbc.Driver";
    private static  final String url = "jdbc:mysql://129.204.3.133:3306/students";
    private static  final String dbusername = "root";
    private static  final String dbpassword = "2004";
     */
    //从jdbc.properties获取变量值
    private static  String driver;
    private static  String url;
    private static String dbusername;
    private static String dbpassword;


    //利用静态代码块,优先获取属性的值:
    static {
        //创建java中的属性集Properties
        Properties prop = new Properties();
        //直接使用文件路径获得文件输入流
//            FileInputStream ips = new FileInputStream("src\\main\\resources\\jdbc.properties");
//            利用class自带的资源加载方法进行文件输入流的获取
        //解析:https://blog.csdn.net/liu911025/article/details/80415001
        InputStream ips=JdbcConnection.class.getClassLoader()
                .getResourceAsStream("jdbc.properties");
        try {
            prop.load(ips);
            driver =prop.getProperty("driver");
            url = prop.getProperty("url");
            dbusername = prop.getProperty("dbusername");
            dbpassword = prop.getProperty("dbpassword");

        } catch (IOException e) {
            e.printStackTrace();
        }finally {
            try {
                ips.close();//关闭输入流
            } catch (IOException e) {
                e.printStackTrace();
            }
        }


    }

    //获取连接
    public static Connection getCon() throws ClassNotFoundException, SQLException {
        //注册使用哪个驱动
        Class.forName(driver);
        //创建新的连接
        Connection connection= DriverManager.getConnection(url,dbusername,dbpassword);
        return connection;
    }


}

 

再resources目录下新建一个配置文件:jdbc.properties

用于数据源的配置:

driver=com.mysql.jdbc.Driver
url=jdbc:mysql://129.204.3.133:3306/students
dbusername=root
dbpassword=2004

 

实现查询

import connector.JdbcConnection;

import java.sql.*;


public class SelectJdbc {

    public static void main(String[] args) throws SQLException, ClassNotFoundException {
        select();
    }

    public static void select()  {
        try{
            Connection con = JdbcConnection.getCon();
            Statement stat = con.createStatement();
            String selectSql = "select * from weibo_user";
            ResultSet resultSet = stat.executeQuery(selectSql);
            int i = 0;
            while (resultSet.next()){
                //从结果集当中获取username的值,因为username对应的值类型是String,所以
                //使用getString ,括号是表的列名称
                String name = resultSet.getString("username");
                Date date = resultSet.getDate("create_at");
                System.out.print(name+"   ");
                System.out.println(date);
                i++;
                if (i >=10){//控制输出10个数据
                    break;
                }
            }
            //关闭stat
            stat.close();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }

    }

}

 

实现增加:

import connector.JdbcConnection;

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

public class InsertJdbc {
    public static void main(String[] args) throws SQLException, ClassNotFoundException {
        Insert();
    }

    private static void Insert() throws SQLException, ClassNotFoundException {
        Connection con = JdbcConnection.getCon();
        Statement stat = con.createStatement();
        String insertsql = "INSERT INTO  weibo_user(username,password,nickname,status,create_at,remark) VALUES('06zhangsann', '1111', 'unickname', 2, '2020-05-05 10:24:24', '22')";
        int how = stat.executeUpdate(insertsql);//int返回影响的行数
//        boolean how = stat.execute(insertsql);
        /*
        true if the first result is a ResultSet object;
        false if it is an update count or there are no results
         */
        System.out.println("insert"+how);

        stat.close();

    }
}

 

实现更改:

import connector.JdbcConnection;

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

public class UpdateJdbc {
    public static void main(String[] args)  {
        update();
    }

    private static void update() {
        try{
            Connection con = JdbcConnection.getCon();
            Statement stat = con.createStatement();

            String  updatesql = "UPDATE weibo_user SET username='quanupdate' WHERE id = 666669 ";
            int how =stat.executeUpdate(updatesql);
            System.out.println(how);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }

    }
}

 

实现删除:

import connector.JdbcConnection;

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

public class DeleteJdbc {
    public static void main(String[] args) throws SQLException, ClassNotFoundException {
        delete();
    }

    private static void delete() throws SQLException, ClassNotFoundException {
        Connection con = JdbcConnection.getCon();
        Statement stat = con.createStatement();
        String deletesql = "DELETE FROM weibo_user WHERE id =666668";
        int how = stat.executeUpdate(deletesql);
        System.out.println(how);
    }
}

 

多条语句一起执行addbach方法

import connector.JdbcConnection;

import java.sql.*;

public class MoreInsertJdbc {
    public static void main(String[] args) {
//        moreInset();
        moreInsertP();
    }
//使用Statement对象完成多条执行
    public static void moreInset(){
        try{
            Connection con = JdbcConnection.getCon();
            Statement stat = con.createStatement();
            String  insertsql1 = "INSERT INTO  weibo_user(username,password,nickname,status,create_at,remark) " +
                    "VALUES('0611zhangsann', '1111', 'unickname', 2, '2020-05-05 10:24:24', '22')";
            String  insertsql2 = "INSERT INTO  weibo_user(username,password,nickname,status,create_at,remark) " +
                    "VALUES('0622zhangsann', '1111', 'unickname', 2, '2020-05-05 10:24:24', '22')";
            String  insertsql3 = "INSERT INTO  weibo_user(username,password,nickname,status,create_at,remark) " +
                    "VALUES('0633zhangsann', '1111', 'unickname', 2, '2020-05-05 10:24:24', '22')";
            stat.addBatch(insertsql1);
            stat.addBatch(insertsql2);
            stat.addBatch(insertsql3);
            //执行batch
            int[] how = stat.executeBatch();
            for (int i : how) {
                System.out.println(i);
            }
            /*
            1
            1
            1
             */
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    /*
    上面的太复杂了,每次的SQL语句都要写一遍,所以我们使用另外一个执行器PrepareStatement对象
     */
    public static void moreInsertP(){
        try{
            Connection con = JdbcConnection.getCon();
            String insertp = "INSERT INTO  weibo_user(username,password,nickname,status,create_at,remark) " +
                    "VALUES(?,?,?,?,?,?)";
            PreparedStatement pstat = con.prepareStatement(insertp);
            //第一个数据
            pstat.setString(1,"0611zhangsann");
            pstat.setString(2, "1111");
            pstat.setString(3,"unickname");
            pstat.setInt(4 ,2);
            pstat.setString(5, "2020-05-05 10:24:24");
            pstat.setInt(6,22);
            pstat.addBatch();

            //第二个数据
            pstat.setString(1,"0622zhangsann");
            pstat.setString(2, "1111");
            pstat.setString(3,"unickname");
            pstat.setInt(4 ,2);
            pstat.setString(5, "2020-05-05 10:24:24");
            pstat.setInt(6,22);
            pstat.addBatch();
//好像也没有这么简单
            //执行:
            int[] how =pstat.executeBatch();
            for (int i : how) {
                System.out.println(i);
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }


    }
}

 

关于事务,jdbc也是支持的:

 

在开发中,我们对数据库的多个表或对一个表的多条数据执行更新操作的时候,要保证多个更新操作要么同时成功、要么都不成功。这就涉及到多个更新操作的事务管理问题了

 

例子:

银行的转账问题,A用户向B用户转账100元,假设A用户和B用户的钱都存储在Account表中,那么A向B转账就涉及同时更新Account表中的A用户的钱和B用户的钱,不然的话,A的钱少了,而B却没有收到钱,这是不允许出现的事件。

 

update account set money = money -100 where name = 'A';
update account set money = money + 100 where name = 'B';

事务能够控制何时更改提交并应用于数据库。它将单个SQL语句或一组SQL语句视为一个逻辑单元,如果任何语句失败,整个事务将失败。

 

注意,

JDBC连接默认是处于自动提交,我们需要手东的关闭自动提交

conn.setAutoCommit(false);

所有操作无误执行后进行提交:

con.commit();

否则回滚;

con.rollback();

 

更高级的用法:

Savepoint对象,主要是建立快照,方便回滚到指定地点:

setSavepoint(String savepointName);//定义新的保存点,返回`Savepoint`对象。
releaseSavepoint(Savepoint savepointName);//删除保存点。参数是由上面的方法生出的对象。

一旦设立指定快照点后,救可以回滚

使用rollback(String savepointName)方法,就可以将事务回滚到指定的保存点

 

 

 

 

import connector.JdbcConnection;

import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Savepoint;
import java.sql.Statement;

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

    }
    public static void Com() throws SQLException, ClassNotFoundException {
        Connection con = JdbcConnection.getCon();
        Savepoint sp = null;
        //注意变量的域范围
        try {
            con.setAutoCommit(false);
            sp = con.setSavepoint("firstPoint");
            Statement stat = con.createStatement();

            String sql = "INSERT INTO  weibo_user(username,password,nickname,status,create_at,remark) VALUES('06zhangsann', '1111', 'unickname', 2, '2020-05-05 10:24:24', '22')";

            int how = stat.executeUpdate(sql);
            //因为JDBC默认是自动提交的,把自动提交关闭
            con.commit();

        } catch (SQLException throwables) {
            con.rollback(sp);//回滚
        }

    }


}

 

 

jdbc还提供获取数据库和表元数据的累:

import connector.JdbcConnection;

import java.sql.*;

public class MetaDatabase {
    public static void main(String[] args) {
        getMeta();
    }

    private static void getMeta(){
        try{
            Connection con = JdbcConnection.getCon();

            //数据库元数据
            DatabaseMetaData dbMeta = con.getMetaData();
            System.out.println("数据库请求地址:"+dbMeta.getURL());
            System.out.println("数据库系统函数:"+ dbMeta.getSystemFunctions());
            System.out.println("登录数据库的用户:"+dbMeta.getUserName());
            System.out.println("数据库名i在:"+dbMeta.getDatabaseProductName());
            System.out.println("驱动版本:"+dbMeta.getDriverVersion());

            //数据表元数据
            Statement stat = con.createStatement();
            String sql ="SELECT * FROM weibo_user";
            ResultSet rset = stat.executeQuery(sql);
            ResultSetMetaData rdate = rset.getMetaData();
            int count = rdate.getColumnCount();
            System.out.println("表字段总数:"+count);
            for (int i=1;i<=count;i++){
                System.out.println("第"+i+"列的字段名:"+rdate.getColumnLabel(i)+"   类型:"+rdate.getColumnTypeName(i));
            }


        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }


    }
}

 

结果:

数据库请求地址:jdbc:mysql://129.204.3.133:3306/students
数据库系统函数:DATABASE,USER,SYSTEM_USER,SESSION_USER,PASSWORD,ENCRYPT,LAST_INSERT_ID,VERSION
登录数据库的用户:root@163.125.239.90
数据库名i在:MySQL
驱动版本:mysql-connector-java-5.1.49 ( Revision: ad86f36e100e104cd926c6b81c8cab9565750116 )
表字段总数:7
第1列的字段名:id   类型:INT
第2列的字段名:username   类型:VARCHAR
第3列的字段名:password   类型:VARCHAR
第4列的字段名:nickname   类型:VARCHAR
第5列的字段名:status   类型:SMALLINT
第6列的字段名:create_at   类型:DATETIME
第7列的字段名:remark   类型:VARCHAR

 

同时提供获取主键的累:

import connector.JdbcConnection;

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

/*
获取表中的主键值
 */
public class GetGeneratedKeys {
    public static void main(String[] args) {
        getKeys();
    }

    public static void getKeys(){
        try{
            Connection con = JdbcConnection.getCon();
            Statement stat = con.createStatement();
            //查询语句是获取不了主键值的
//            String sql = "select * from weibo_user";
//            stat.executeQuery(sql,Statement.RETURN_GENERATED_KEYS);
            String insertsql = "INSERT INTO  weibo_user(username,password,nickname,status,create_at,remark) VALUES('0655zhangsann', '1111', 'unickname', 2, '2020-05-05 10:24:24', '22')";
            stat.executeUpdate(insertsql,Statement.RETURN_GENERATED_KEYS);
            ResultSet rset = stat.getGeneratedKeys();
            while (rset.next()){
                int id = rset.getInt(1);
                /*
                int id = rset.getInt(2);因为组件返回的结果集只有一个,所以2就会报错,数字是指,index索引
                java.sql.SQLException: Column Index out of range, 2 > 1.
                 */
                System.out.println(id);
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }
}

 

但是每一个SQL操作都创建一个连接,消耗的系统资源比较大,这时候就利用到了连接池

关于连接池的介绍可以看看

https://www.cnblogs.com/java-quan/p/13190779.html
  

第一个连接池:dbcp

import connector.JdbcConnection;
import org.apache.commons.dbcp2.BasicDataSource;


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

public class JdbcConnectionSource {
    private static String driver;
    private static String url;
    private static String dbusername;
    private static String dbpassword;
    private static BasicDataSource dataSource;

    static {
        Properties prop = new Properties();
        InputStream ips = JdbcConnectionSource.class.getClassLoader()
                .getResourceAsStream("jdbc.properties");
        try {
            prop.load(ips);
            driver = prop.getProperty("driver");
            url = prop.getProperty("url");
            dbusername = prop.getProperty("dbusername");
            dbpassword = prop.getProperty("dbpassword");
            //设置datasource
            dataSource = new BasicDataSource();
            dataSource.setUrl(url);
            dataSource.setDriverClassName(driver);
            dataSource.setUsername(dbusername);
            dataSource.setPassword(dbpassword);

            //初始化datasouce参数设置
            dataSource.setInitialSize(3);
            dataSource.setMaxTotal(5);
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                ips.close();//关闭输入流
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

    public static Connection getCon() throws SQLException {
        Connection connection = dataSource.getConnection();
        return connection;
    }


}

之后也是直接使用getCon方法进行连接的获取。

 

 

第二个是阿里云的druid连接池:

需要引入依赖包:

        <!--上面的dbcp连接池性能不好,用阿里云的 https://mvnrepository.com/artifact/com.alibaba/druid -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.1.22</version>
        </dependency>

 

使用连接池;

import com.alibaba.druid.pool.DruidDataSource;

import java.io.InputStream;
import java.util.Properties;

public class JdbcDruidSource {
    private static String driver;
    private static String url;
    private static String dbusername;
    private static String dbpassword;
    private static DruidDataSource dataSource;

    static {
        try{
            Properties prop = new Properties();
            InputStream ips = JdbcDruidSource.class.getClassLoader()
                    .getResourceAsStream("jdbc.properties");
            dataSource = new DruidDataSource();
            dataSource.setUrl(url);
            dataSource.setUsername(dbusername);
            dataSource.setPassword(dbpassword);


        }catch (Exception e){
            e.printStackTrace();
        }
    }


}

 

posted @ 2020-06-25 00:11  小丑quan  阅读(249)  评论(0)    收藏  举报