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();
}
}