package com.shaogq.review.jdbc.connectionDB;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
public class Main {
private static final Log log = LogFactory.getLog(Main.class);
public static void main(String[] args) {
String url = "jdbc:mysql://localhost:3306/corejava";
String username = "root";
String password = "12345678";
// 1.注册驱动器类
// 1.(1)自动注册驱动器类(Java Standard Edition Service Provider)
// 包含META-INF/services/java.sql.Driver文件的JAR文件可以自动注册
// 1.(2)在JAVA程序中加载驱动器类
try {
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
// 1.(3)通过设置jdbc.drivers属性,并用冒号将他们分割
// System.setProperty("jdbc.drivers",
// "com.mysql.jdbc.Driver:oracle.jdbc.driver.OracleDriver");
// 1.(4)直接通过DriverManager.registerDriver
// try {
// DriverManager.registerDriver(new com.mysql.jdbc.Driver());
// } catch (SQLException e) {
// e.printStackTrace();
// }
try {
// 2.建立数据库连接
// 2.(1)建立Connection空对象
Connection conn = null;
// 3.建立Statement对象,用于执行静态SQL语句并返回它所生成的结果对象
// 3.(1)建立Statement空对象
Statement stat = null;
try {
// 2.(2)通过DriverManager.getConnection方法所获得的Connection对象
conn = DriverManager.getConnection(url, username, password);
// 7.(1)事务:关闭自动提交模式
conn.setAutoCommit(false);
try {
/**
* 3.(2)创建可滚动和可更新的结果集
* ResultSet的type值:
* TYPE_FORWARD_ONLY 结果集不能滚动;
* TYPE_SCROLL_INSENSITIVE 结果集可以滚动,但对数据库的变化不敏感;
* TPYE_SCROLL_SENSITIVE 结果集可以滚蛋,且对数据库的变化敏感.
*
* ResultSet的Concurrency值:
* CONCUR_READ_ONLY 结果集不能用于更新数据库(默认值);
* CONCUR_UPDATABLE 结果集可以用于更新数据库.
*
*/
Statement stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
ResultSet.CONCUR_READ_ONLY);
// 4.准备要执行的SQL语句
// 4.(1)executeUpdate 用于执行除SELECT外的DML(UPDATE、INSERT、DELETE)
// 和DDL(CREATE、ALTER、DROP等);
// executeUpdate 对于 SQL 数据操作语言 (DML) 语句,返回行计数 (2)
// 对于什么都不返回的 SQL 语句,返回 0
// 5.执行SQL语句
// 5.(1)通过executeUpdate执行删表语句
String DropTable = "DROP TABLE IF EXISTS test_table";
int DropTableResult = stmt.executeUpdate(DropTable);
log.info("DropTableResult = " + DropTableResult + "== 0");
// 5.(2)通过executeUpdate执行建表表语句
String createTable = "CREATE TABLE IF NOT EXISTS test_table ("
+ "id INT(11) NOT NULL AUTO_INCREMENT, "
+ "name varchar(20), " + "PRIMARY KEY (`id`))";
int createTableResult = stmt.executeUpdate(createTable);
log.info("createTableResult = " + createTableResult + "== 0");
// 5.(3)通过executeUpdate执行插入操作
String insertData = "INSERT INTO test_table (name) VALUES('a')";
int insertDataResult = stmt.executeUpdate(insertData);
log.info("insertDataResult = " + insertDataResult + "== 0");
// 3.(3)或预备语句PreparedStatement :
// PreparedStatement prepStmt =
// conn.prepareStatement(command, type, concurrency);
String insertPreparedData = "INSERT INTO test_table (name) VALUES(?)";
PreparedStatement prepStmt = conn.prepareStatement(
insertPreparedData, ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_READ_ONLY);
String[] names = new String[]{"b", "c", "d"};
// 5.(4)通过PreparedStatement执行批量插入操作
for(int i=0;i<names.length;i++){
prepStmt.setString(1, names[i]);
//将一组参数添加到此 PreparedStatement 对象的批处理命令中
prepStmt.addBatch();
}
int[] executeBatchResults = prepStmt.executeBatch();
for(int i=0;i<executeBatchResults.length;i++){
log.info(i + " value : " + executeBatchResults[i]);
}
prepStmt.close();
// 4.(2)执行SELECT方法必须使用executeQuery方法
String query = "SELECT id,name FROM test_table";
// 5.(5)通过executeQuery查询结果集
ResultSet queryResult = stmt.executeQuery(query);
while(queryResult.next()){
log.info(queryResult.getInt(1) + " " +queryResult.getString(2));
}
// 5.(6)通过execute进行查询,并确定是否存在结果集
// 如果有,则通过元数据遍历结果集的表头和值
log.info("execute");
boolean hasResult = stmt.execute(query);
if(hasResult){
queryResult = stmt.getResultSet();
// 6.(1)得到ResultSet的元数据
ResultSetMetaData metaData = queryResult.getMetaData();
int columnCount = metaData.getColumnCount();
log.info("columnCount = " + columnCount);
while (queryResult.next()) {
for (int i = 1; i <= columnCount; i++) {
log.info("第" + i + "行 "
+ metaData.getColumnLabel(i) + " = "
+ queryResult.getString(i));
}
}
queryResult.close();
}
// 6.(2)得到Connection的元数据DatabaseMetaData,并打印表的信息
log.info("DatabaseMetaData");
DatabaseMetaData meta = conn.getMetaData();
// 从数据库连接中获取一个DatabaseMetaData对象
ResultSet result = meta.getTables(null, null, null,
new String[] { "TABLE" });
while (result.next()) {
//该结果集每行包含数据库中一张表的信息,第三列是表的名称。
log.info(result.getString(3));
}
} catch (Exception e) {
// 7.(2)事务:回滚
conn.rollback();
e.printStackTrace();
}
} finally {
// 7.(2)事务:提交事务
conn.commit();
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
(十二) 整合DEMO
最新推荐文章于 2023-07-17 01:02:49 发布