jdbc

一.定义：java database connector

二.常用的接口

       java.sql.Driver  驱动

       java.sql.Connection  链接

　　java.sql.Statement    静态处理块

　　java.sql.PreparedStatement   预处理模块

　　java.sql.ResultSet  结果集

　　java.sql.ResultSetMeteData  数据库信息数据集

 

三.使用方法：

　　注意：oracle和mysql的jdbc 的jar包是不一样的

　　1.导入jar包

　　2.编译jar包

 

url=jdbc:oracle:thin:@localhost:1521:ORCLusername=SCOTTpassword=root

 

public class DBUtils {
    private static String url = ""; private static String username = ""; private static String password = ""; static{ try { //加载驱动 Class.forName("oracle.jdbc.driver.OracleDriver"); //通过当前的线程获取类加载器，再获取资源获取器（不然不能读取到相对路径） InputStream stream = Thread.currentThread() .getContextClassLoader() .getResourceAsStream("dao/db.properties"); //通过properties读取流中的配置信息 Properties properties = new Properties(); properties.load(stream); url = properties.getProperty("url"); username = properties.getProperty("username"); password = properties.getProperty("password"); } catch (ClassNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } } /** * 获取链接的方法 * @return */ public static Connection getConnection(){ Connection connect = null; //创建驱动对象 Driver driver = new OracleDriver(); try { //将驱动对象加入到驱动对象管理器中 DriverManager.deregisterDriver(driver); //通过驱动对象管理器获取链接 connect = DriverManager .getConnection(url,username,password); } catch (SQLException e) { e.printStackTrace(); } return connect; } /** * 获取sql语句传达器 * @return */ public Statement getStatement(){ Statement st = null; try { st = new DBUtils().getConnection().createStatement(); } catch (SQLException e) { e.printStackTrace(); } return st; } /** * 获取编译好的PrepareStatement * @param psql 带问号的sql语句 * @param args 与问好相对应的参数 * @return */ public PreparedStatement getPrepareStatement(String psql,Object... args){ PreparedStatement pst = null; //获取链接 Connection conn = new DBUtils().getConnection(); try { //产生预编译执行器 pst = conn.prepareStatement(psql); for (int i = 0; i < args.length; i++) { //给预编译注入参数 pst.setObject(i+1,args[i]); } ResultSet resultSet = pst.executeQuery(); } catch (SQLException e) { e.printStackTrace(); } return pst; } /** * 释放Connection PrepareStation ResultSet的资源 * @param conn */ public void release(AutoCloseable... conn){ for (int i = 0; i < conn.length; i++) { try { if(conn[i]!=null){ conn[i].close(); } } catch (Exception e) { e.printStackTrace(); } } } @Test /*** * 测试链接是否成功的方法 */ public void test(){ System.out.println(new DBUtils().getConnection()); } }

 

/**     * 获取表信息数据集     * @return     */    public DatabaseMetaData getDatabaseMetaData(){        try {            return DBUtils.getConnection().getMetaData();        } catch (SQLException e) {            e.printStackTrace();        }        return null;    }    public void getAllTableNames(){        //获取链接        Connection connection =                DBUtils.getConnection();        try {            //获取表数据集            DatabaseMetaData metaData = this.getDatabaseMetaData();            ResultSet tables = metaData.getTables(null, null, null, new String[]{"TABLE"});            while(tables.next()){                //获取表名                String tableName = tables.getString("TABLE_NAME");                System.out.println(tableName);            }        } catch (SQLException e) {            e.printStackTrace();        }    }    //输出列名、类型、注释    public void printColumnInfo(DatabaseMetaData databaseMetaData)throws Exception{        ResultSet rs = databaseMetaData.getColumns(null, "%", "EMP", "%");        while(rs.next()){            //列名            String columnName = rs.getString("COLUMN_NAME");            //类型            String typeName = rs.getString("TYPE_NAME");            //注释            String remarks = rs.getString("REMARKS");            System.out.println(columnName + "--" + typeName + "--" + remarks);        }    }

 

//测试基础查询语句    public void testStatement(){        Connection conn =                new DBUtils().getConnection();        try(Statement statement = conn.createStatement()){            ResultSet resultSet = statement.executeQuery("SELECT * FROM EMP");            while(resultSet.next()){                System.out.print(resultSet.getString("ENAME")+" ");                System.out.println(resultSet.getInt("SAL"));            }        } catch (SQLException e) {            e.printStackTrace();        }    }    /**     * 当要大量插入数据的时候使用batch更快     */    public void testBatch(){        Connection conn = DBUtils.getConnection();        try {            PreparedStatement ps = conn.prepareStatement("INSERT INTO EMPTEMP(NAME,AGE) VALUES(?,?)");            for (int i = 1; i < 1000; i++) {                ps.setString(1,"小王"+i);                ps.setInt(2,i);                //将当前的指令存储到batch中                ps.addBatch();            }            ps.executeBatch();            ps.close();            conn.close();        } catch (SQLException e) {            e.printStackTrace();        }    }