建立數(shù)據(jù)庫連接
//1.使用Driver連接數(shù)據(jù)庫，創(chuàng)建Driver實現(xiàn)類對象
Driver driver = new Driver();
//2.準備連接數(shù)據(jù)庫的基本信息
String url = "jdbc:mysql://127.0.0.1:3306/test";
Properties info = new Properties();
info.put("user", "root");
info.put("password", "root");
//3.調(diào)用Driver的接口connect（url解虱， info） 獲取數(shù)據(jù)庫連接
Connection connection = driver.connect(url, info);
簡化連接不同數(shù)據(jù)庫殴泰，將連接數(shù)據(jù)庫信息存儲到文件中
InputStream in = TestJDBC.class.getClassLoader().getResourceAsStream("jdbc.properties");
Properties properties = new Properties();
properties.load(in);
driverClass = properties.getProperty("driver");
jdbcUrl = properties.getProperty("jdbcUrl");
user = properties.getProperty("user");
password = properties.getProperty("password");
Driver driver = (Driver)Class.forName(driverClass).newInstance();
Properties info = new Properties();
info.put("user", user);
info.put("password", password);
Connection connection = driver.connect(jdbcUrl, info);
通過獲取驅(qū)動連接數(shù)據(jù)庫悍汛，可同時獲得多個驅(qū)動
Class.forName(driverClass);
Connection connection = DriverManager.getConnection(jdbcUrl, user, password);
執(zhí)行Sql語句
insert至会，update， delete操作
//1.獲取數(shù)據(jù)庫連接
Connection conn = getConnection();
//2.準備sql語句
String sql = "insert into customers(name, email, birth) Values('Wayne', 'wayne@gmail.com', '1993-03-16');";
//3.執(zhí)行插入
//獲取Statement對象
Statement statement = conn.createStatement();
//執(zhí)行sql語句昆著，只能是insert术陶，update，delete
statement.executeUpdate(sql);
//4.關(guān)閉連接接谨，在finally中關(guān)閉
statement.close();
conn.close();
select操作
通過調(diào)用 Statement 對象的 excuteQuery() 方法創(chuàng)建該對象
ResultSet 對象以邏輯表格的形式封裝了執(zhí)行數(shù)據(jù)庫操作的結(jié)果集
-
ResultSet 對象維護了一個指向當前數(shù)據(jù)行的游標疤坝，初始的時候馆铁，游標在第一行之前埠巨，可以通過 ResultSet 對象的 next() 方法移動到下一行
//1.獲取數(shù)據(jù)庫連接 Connection conn = getConnection(); //2.獲取Statement Statement statement = conn.createStatement(); //3.準備sql語句 String sql = "select * from customers"; //4.執(zhí)行查詢 ResultSet rs = statement.executeQuery(sql); //5.處理ResultSet while(rs.next()){ int id = rs.getInt(1); String name = rs.getString("name"); String email = rs.getString(3); Date birth = rs.getDate(4); System.out.println(id + " " + name + " " + email + " " + birth); }
PreparedStatement
提高性能
防止sql注入
-
便捷sql語句拼寫
//1.創(chuàng)建PreparedStatement
String sql = "insert into customers(name, email, birth) Values(?,?,?);";
PreparedStatement ps = conn.prepareStatement(sql);//2.賦值 ps.setString(1, "Luke"); ps.setString(2, "Luke@gmail.com"); ps.setDate(3, new Date(new java.util.Date().getTime())); //3.執(zhí)行 ps.executeUpdate();
使用ResultSetMetaData和反射辣垒，簡化查詢勋桶，構(gòu)造方法完成對不同表查詢，結(jié)果生成不同對象
public static <T> T testResultSetMetaData(Class<T> clazz, String sql, Object...objects) throws Exception{
//1.建立連接
Connection conn = getConnection();
//2.獲取PreparedStatement
PreparedStatement ps = conn.prepareStatement(sql);
//3.賦值
for (int i = 0; i<objects.length; i++){
ps.setObject(i+1, objects[i]);
}
//4.執(zhí)行捐韩，并使用ResultSet接受
ResultSet re = ps.executeQuery();
if(re.next()){
//5.創(chuàng)建clazz對象
T t = clazz.newInstance();
//6荤胁、獲取ResultSetMetaData屎债，并放入對象
ResultSetMetaData rsmd = re.getMetaData();
for(int i=0; i<rsmd.getColumnCount(); i++){
String columnLable = rsmd.getColumnLabel(i+1);
Object columnValue = re.getObject(i+1);
//為對象屬性賦值
Field field = clazz.getDeclaredField(columnLable);
field.setAccessible(true);
field.set(t, columnValue);
}
return t;
} else {
return null;
}
}
存儲過程
//1.建立連接
Connection conn = getConnection();
//2.獲取CallableStatement
String sql = "{? = call sum_salary(?,?)}";
CallableStatement callableStatement = conn.prepareCall(sql);
//3.注冊參數(shù)
callableStatement.registerOutParameter(1,Types.NUMERTIC);
callableStatement.registerOutParameter(3,Types.NUMERTIC);
//4.給參數(shù)賦值
callableStatement.setInt(2,80);
//5.執(zhí)行
callableStatement.execute();
//6.獲取執(zhí)行結(jié)果
double sunSalary = callableStatement.getDouble(1);
long empCount = callableStatement.getLong(3);
小結(jié)
- java.sql.DriverManager用來裝載驅(qū)動程序圆丹，獲取數(shù)據(jù)庫連接召娜。
- java.sql.Connection完成對某一指定數(shù)據(jù)庫的聯(lián)接
- java.sql.Statement在一個給定的連接中作為SQL執(zhí)行聲明的容器，他包含了兩個重要的子類型秸讹。
- Java.sql.PreparedSatement 用于執(zhí)行預編譯的sql聲明
- Java.sql.CallableStatement用于執(zhí)行數(shù)據(jù)庫中存儲過程的調(diào)用
java.sql.ResultSet對于給定聲明取得結(jié)果的途徑
