JDBC-基础

来源：互联网发布：linux下部署jira 编辑：程序博客网时间：2024/06/07 09:09

一、JDBC的主要接口

JDBC的主要接口和管理类
获取一个Connetctionl链接
获取JDBC的connetction

Connection
主要负责数据库的链接，事物处理，创建执行语句的Statement。
Statement、PreparedStatement、CallableStatement
都是执行语句的申明，差异在PreparedStatement可以通过setXXX来设置参数，CallableStatement主要用来调用存储过程，可以从存储过程中得到返回的值getXXX(int parm)。
ResultSet
执行查询结果返回的结果集，可以循环获取数据，最主要的方法是根据位置和根据名字获取值。

二、获取数据实例

package com.elements.myjdbc;import java.io.IOException;import org.junit.Test;import java.sql.DriverManager;import java.sql.Connection;import java.sql.Statement;import java.sql.ResultSet;import java.sql.SQLException;public class TestJDBC01 {    @Test    public void TestJDBCSelect() throws IOException {        long aaa = System.nanoTime();        Connection conn = null;        Statement stmt = null;        ResultSet rs = null;        try {            //需要的驱动            Class.forName("com.mysql.cj.jdbc.Driver");            //数据库连接地址、用户名、密码            String url = "jdbc:mysql://localhost:3306/mybatis?characterEncoding=utf8&useSSL=true&serverTimezone=UTC";            //获取数据库的链接            conn = DriverManager.getConnection(url, "mybatis", "mybatis");            //用Connection来创建执行语句的Statement             stmt = conn.createStatement();            //执行查询语句，返回数据集             rs = stmt.executeQuery("select * from user");            while (rs.next()) {                //根据序号获取数据                System.out.print("userId: " + rs.getInt(1));                System.out.print("\t UserName: " + rs.getString(2));                System.out.println("\tUserEmail: " + rs.getString(3));            }        } catch (ClassNotFoundException e) {            System.out.println("找不到指定的驱动程序类!");        } catch (SQLException e) {            e.printStackTrace();        } finally {            try {                if (rs != null)                    rs.close();//关闭数据集                if (stmt != null)                    stmt.close();//关闭执行语句                if (conn != null)                    conn.close();//关闭连接            } catch (SQLException e) {                // TODO Auto-generated catch block                e.printStackTrace();            }        }        long bbb = System.nanoTime();        System.out.print((bbb - aaa));    }}

三、创建表、查询、新增，更新、删除、删除表

            Statement stmt = conn.createStatement();            //创建表            stmt.execute("create table student(sid char(10), name char(10), age int)");                    //新增数据             stmt.executeUpdate("insert into student values('J001','张三',18)");            stmt.executeUpdate("insert into student values('J002','李四',20)");            stmt.executeUpdate("insert into student values('J003','王五',19)");            //查询数据             ResultSet rs = stmt.executeQuery("select * from student");            while (rs.next()) {                System.out.print("学号: " + rs.getString(1));                System.out.print("\t姓名: " + rs.getString(2));                System.out.println("\t年龄: " + rs.getInt(3));            }            rs.close();            //更新数据            stmt.executeUpdate("update student set age=age+10");            //删除数据            stmt.executeUpdate("delete from student where sid='J002'");            System.out.println("--------------更新/删除数据后---------------");            rs = stmt.executeQuery("select * from student");            while (rs.next()) {                System.out.print("学号: " + rs.getString("sid"));                System.out.print("\t姓名: " + rs.getString("name"));                System.out.println("\t年龄: " + rs.getInt("age"));            }            //删除创建的表            stmt.execute("drop  table student");

四、PreparedStatement使用

            Statement stmt = conn.createStatement();             stmt.execute("create table mytable2(id int, name char(10), age DECIMAL(20,0))");                 //sql语句？的地方就是PreparedStatement后面要替换的地方            String sql = "insert into mytable2 values(?,?,?)";                  PreparedStatement  pst = conn.prepareStatement(sql);                        for(int i = 101;i<200;i++){                //根据位置设置值                pst.setInt(1,i);                pst.setString(2,"Tom" + i);                 pst.setDouble(3,(100+i)*10);                                                pst.executeUpdate();            }            //以下雷同            System.out.println("id:\tname:\tsalary:");              pst = conn.prepareStatement("select * from mytable2 where id = ?");            pst.setInt(1,157);            ResultSet rs = pst.executeQuery();            if(rs.next()){                System.out.println(rs.getInt(1) + "\t" + rs.getString(2).trim() + "\t" + rs.getDouble(3));              }            int[] ids = {160,171,182};            for(int i:ids){                pst.setInt(1,i);                rs = pst.executeQuery();                if(rs.next()){                    System.out.println(rs.getInt(1) + "\t" + rs.getString(2).trim() + "\t" + rs.getDouble(3));                  }            }            stmt.execute("drop table mytable2");

五、用CallableStatement调用存储过程

过程的创建及测试

CREATE PROCEDURE test_procedure_01(in a int ,in b int ,inout c int)BEGIN   //业务 INSERT INTO user (`UserName`, `UserEmail`) VALUES ('5', '6');   set c=a+b;END //测试，注意第三个参数是inoutSET @p_out=1;CALL test_procedure_01(5, 6,@p_out); SELECT @p_out;

输出结果

- JAVA调用

            //test_procedure_01是存储过程，？是需要传递的参数            CallableStatement cst = conn.prepareCall("{call test_procedure_01(?,?,?)}");                        cst.setInt(1,1001);            cst.setInt(2,2222);            int aaaa=0;            cst.setInt(3,aaaa);            System.out.println(aaaa);//这里没有任何变化，需要注意            cst.execute();            //从CallableStatement 获取返回值，第三个参数是out value             aaaa=cst.getInt(3);            System.out.println(aaaa);

六、批量数据新增

            conn.setAutoCommit(false); //这里最好是一次提交，不要设置自动提交            Statement stmt = conn.createStatement();            stmt.execute("create table mytable2(id int, name char(10), salary DECIMAL(20,0))");             //增加Batch            stmt.addBatch("insert into mytable2 values(11,'Tom',2500)");            stmt.addBatch("insert into mytable2 values(12,'John',3400)");            stmt.addBatch("insert into mytable2 values(13,'Billy',3150)");            stmt.addBatch("update mytable2 set salary = salary + 88");            //对于大批量数据的提交 这种方式比较好            stmt.executeBatch();            conn.commit();            ResultSet rs = stmt.executeQuery("select * from mytable2");            while(rs.next()){                System.out.println(rs.getInt(1) + "\t" + rs.getString(2).trim() + "\t" + rs.getDouble(3));              }            stmt.execute("drop table mytable2");

七、事务处理

原来数据表情况
这里写图片描述

rollback 不带参数

            conn.setAutoCommit(false);             Statement stmt = conn.createStatement();            conn.setSavepoint("Uninsert");            stmt.executeUpdate("INSERT INTO user (UserName, UserEmail) VALUES ('1111', '11111');");            conn.setSavepoint("Insert111");            stmt.executeUpdate("INSERT INTO user (UserName, UserEmail) VALUES ('2222', '2222');");            conn.rollback();

rollback 将我们新增的2条记录全部回滚。数据库表无新增记录

rollback 带参数

            conn.setAutoCommit(false);              Statement stmt = conn.createStatement();            Savepoint Uninsert =conn.setSavepoint("Uninsert");            stmt.executeUpdate("INSERT INTO user (UserName, UserEmail) VALUES ('1111', '11111');");            Savepoint Insert111=conn.setSavepoint("Insert111");            stmt.executeUpdate("INSERT INTO user (UserName, UserEmail) VALUES ('2222', '2222');");            conn.rollback(Insert111);            conn.commit();

在这里我们回滚到了Insert111这个保存好的事务点，这个点我们已经新增了1111记录，2222记录被回滚，查看数据我们可以得到以下结果：
这里写图片描述

conn.rollback(Uninsert );和不带参数的rollback一样既无记录新增。

八、获取数据库元数据

            ResultSet rs = null;                    DatabaseMetaData dmd = conn.getMetaData();             if (dmd == null) {                 System.out.println ("No Meta available");             } else {                 System.out.println ("数据库名称:" + dmd.getDatabaseProductName());                 System.out.println ("数据库版本:" + dmd.getDatabaseProductVersion());                 System.out.println ("数据库驱动程序：" + dmd.getDriverName());                 System.out.println ("驱动程序版本号：" + dmd.getDriverVersion());                 System.out.println ("并发访问的用户个数" + dmd.getMaxConnections());                 System.out.println ("数据类型列表：" );                 rs = dmd.getTypeInfo();                while(rs.next()){                    System.out.println("\t" + rs.getString(1));                 }                rs.close();            }             //select * from information_schema.tables  mysql所有表            //select * from information_schema.columns  表內所有的名字            Statement stmt = conn.createStatement();            String s = "select * from user";            rs = stmt.executeQuery(s);                                  System.out.println("数据表user结构信息：");            ResultSetMetaData  rsm = rs.getMetaData();            int columnCount = rsm.getColumnCount();            System.out.println("列序号\t列名\t数据类型");            for(int i=1;i<=columnCount;i++){                System.out.println(" " + i + " \t" +                 rsm.getColumnName(i) + "\t" +                 rsm.getColumnTypeName(i));                          }

输出结果

数据库名称:MySQL数据库版本:5.6.26-log数据库驱动程序：MySQL Connector Java驱动程序版本号：mysql-connector-java-6.0.2 ( Revision: c6da0fe501ad43d4ed6483b60ea796dc9fbe2d7b )并发访问的用户个数0数据类型列表：    BIT    BOOL    TINYINT    TINYINT UNSIGNED    BIGINT    BIGINT UNSIGNED    LONG VARBINARY    MEDIUMBLOB    LONGBLOB    BLOB    VARBINARY    TINYBLOB    BINARY    LONG VARCHAR    MEDIUMTEXT    LONGTEXT    TEXT    CHAR    ENUM    SET    DECIMAL    NUMERIC    INTEGER    INTEGER UNSIGNED    INT    INT UNSIGNED    MEDIUMINT    MEDIUMINT UNSIGNED    SMALLINT    SMALLINT UNSIGNED    FLOAT    DOUBLE    DOUBLE PRECISION    REAL    VARCHAR    TINYTEXT    DATE    YEAR    TIME    DATETIME    TIMESTAMP数据表user结构信息：列序号 列名  数据类型 1  userId  INT 2  UserName    VARCHAR 3  UserEmail   VARCHAR 4  createTime  VARCHAR 5  updateTime  VARCHAR

如果要访问mysql的数据表和表内的字段可以通过下面2个表获取数据，也可以通过上面例子的方式
select * from information_schema.tables mysql所有表
select * from information_schema.columns 表內所有的列名

项目代码下载地址：http://pan.baidu.com/s/1skPpdVZ

0 0