2019-4-22 jdbc学习笔记
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:ORCL username=SCOTT password=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(); } }