實驗名稱：集元數(shù)據(jù)練習
實驗目的：
1、集元數(shù)據(jù)的相關(guān)概念慨亲；
2、學會DatabaseMetaData宝鼓，并可獲取數(shù)據(jù)庫的相關(guān)信息刑棵；
3、學會ResultSetMetaData愚铡，可以通過該類的對象獲取結(jié)果集的信息铐望，并對查詢結(jié)果進行處理。
實驗內(nèi)容：
在數(shù)據(jù)庫中創(chuàng)建學生表Student，自主設置學生表的信息正蛙。
對學生表進行查詢操作，并將查詢結(jié)果輸出（包括字段名稱）营曼。
實驗步驟：
/*
該版本的代碼并沒有使用查詢結(jié)果集與類對應起來乒验，并且未
對結(jié)果集進行封裝，也沒有使用相應的反射機制蒂阱，所以總體
說數(shù)據(jù)的安全性和封裝性不能很好的體現(xiàn)锻全，將對改代碼進行改進
*/
package metadata;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.Properties;
public class DBUTilsTools {
public static Connection getConnection() {
Connection conn = null;//創(chuàng)建連接對象
InputStream is = ClassLoader.getSystemResourceAsStream("jdbc.properties");//創(chuàng)建流對象
Properties properties = new Properties();
try {
properties.load(is);
String driver = properties.getProperty("driver");
String url = properties.getProperty("url");
String userName = properties.getProperty("user");
String password = properties.getProperty("password");
Class.forName(driver);
conn = DriverManager.getConnection(url, userName, password);//使用連接對象連接數(shù)據(jù)庫
} catch (IOException e1) {
e1.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
}
return conn;//將創(chuàng)建好的數(shù)據(jù)庫連接對象返回
}
public static int exeUpdate(String sql,Object...values) {//執(zhí)行多參數(shù)sql
Connection conn = null;
conn = DBUTilsTools.getConnection();//獲取連接對象
PreparedStatement pstmt = null;//預定義sql
int n = 0;
try {
pstmt = conn.prepareStatement(sql);
for(int i = 1;i <= values.length; i++){
pstmt.setObject(i, values[i-1]);
}
n = pstmt.executeUpdate();//獲取sql執(zhí)行結(jié)果
} catch (SQLException e) {
e.printStackTrace();
}finally {
DBUTilsTools.release(conn, pstmt, null);//釋放連接資源
}
return n;
}
public static void exeQuery(String sql,Object...values) {
Connection conn = null;
conn = DBUTilsTools.getConnection();
PreparedStatement pstmt = null;
ResultSet rs = null;//結(jié)果集
try {//相當于sql語句中？(占位符)的個數(shù)录煤，鳄厌？也就是所指定字段的名稱
pstmt = conn.prepareStatement(sql);
for (int i = 1; i <= values.length; i++) {//該循環(huán)的作用可以認為是將sql語句將之前空缺的占位符補充完整
pstmt.setObject(i, values[i-1]);
}
//真正的執(zhí)行sql語句
rs = pstmt.executeQuery();//查詢結(jié)束后，返回的結(jié)果中包含你所需的那幾列
//獲取結(jié)果集的結(jié)構(gòu)
ResultSetMetaData rsmd = rs.getMetaData();
int count = rsmd.getColumnCount();//獲取結(jié)果集的列數(shù)
for(int i = 1; i <= count; i++) {
System.out.print(rsmd.getColumnName(i) + "\t\t");//輸出每一列的名稱
}
System.out.println();//換行
while(rs.next()) {//光標移動到下一行
for(int i = 1;i <= count; i++) {//遍歷所查詢的列的值
System.out.print(rs.getObject(i) + "\t");//打印輸出所有列的內(nèi)容
}
System.out.println();
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
DBUTilsTools.release(conn, pstmt, rs);
}
}
//釋放連接
public static void release(Connection conn, PreparedStatement pstmt,ResultSet rs) {
if(rs != null)
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
if(pstmt != null)
try {
pstmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
if(conn != null)
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
//driver codes：
public static void main(String[] args) {
String sql = "select bno,bname,bpublisher,bprice from book";
DBUTilsTools.exeQuery(sql);
}
}
改進代碼
//工具類
package metadataImpro;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;
public class DBUTilsTools {
public static Connection getConnection() {
Connection conn = null;
InputStream is = ClassLoader.getSystemResourceAsStream("jdbc.properties");
Properties properties = new Properties();
try {
properties.load(is);
String driver = properties.getProperty("driver");
String url = properties.getProperty("url");
String userName = properties.getProperty("user");
String password = properties.getProperty("password");
Class.forName(driver);
conn = DriverManager.getConnection(url, userName, password);
} catch (IOException e1) {
e1.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
}
return conn;
}
public static int exeUpdate(String sql,Object...values) {
Connection conn = null;
conn = DBUTilsTools.getConnection();
PreparedStatement pstmt = null;
int n = 0;
try {
pstmt = conn.prepareStatement(sql);
for(int i = 1;i <= values.length; i++){
pstmt.setObject(i, values[i-1]);
}
n = pstmt.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}finally {
DBUTilsTools.release(conn, pstmt, null);
}
return n;
}
public static List exeQuery(String sql,String className,Object...values) {
Connection conn = null;
conn = DBUTilsTools.getConnection();
PreparedStatement pstmt = null;
ResultSet rs = null;
List list = new ArrayList();
try {//相當于sql語句中妈踊？(占位符)的個數(shù)了嚎，？也就是所指定字段的名稱
pstmt = conn.prepareStatement(sql);
for (int i = 1; i <= values.length; i++) {//該循環(huán)的作用可以認為是將sql語句將之前空缺的占位符補充完整
pstmt.setObject(i, values[i-1]);
}
//真正的執(zhí)行sql語句
rs = pstmt.executeQuery();//查詢結(jié)束后廊营，返回的結(jié)果中包含你所需的那幾列
//獲取結(jié)果集的結(jié)構(gòu)
ResultSetMetaData rsmd = rs.getMetaData();
int count = rsmd.getColumnCount();//獲取結(jié)果集的列數(shù)
Class class1 = Class.forName(className);
while(rs.next()) {//光標移動到下一行
Object obj = class1.newInstance();//實例化
for(int i = 1;i <= count; i++) {//遍歷所查詢的列的值
String varname = rsmd.getCatalogName(i);//獲得列的名稱
Object value = rs.getObject(i);//獲取列的值
ReflectionTools.setField(obj, varname, value);//調(diào)用反射方法歪泳，為每一字段賦值
}
list.add(obj);
}
} catch (SQLException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (InstantiationException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
}finally {
DBUTilsTools.release(conn, pstmt, rs);
}
return list;
}
public static void release(Connection conn, PreparedStatement pstmt,ResultSet rs) {
if(rs != null)
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
if(pstmt != null)
try {
pstmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
if(conn != null)
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
public static void main(String[] args) {
String sql = "select bno,bname,bpublisher,bprice from book where bprice=?";
String className = "Book";
List list = DBUTilsTools.exeQuery(sql,className,59.80);
for (Object obj : list) {
System.out.println(obj);
}
}
}
//工具類
package metadataImpro;
import java.lang.reflect.Field;
public class ReflectionTools {
public static void setField(Object obj, String name, Object value) {
Class class2 = obj.getClass();
try {
Field field = class2.getDeclaredField(name);
field.setAccessible(true);
field.set(obj, value);
} catch (NoSuchFieldException e) {
e.printStackTrace();
} catch (SecurityException e) {
e.printStackTrace();
} catch (IllegalArgumentException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
}
}
}
//數(shù)據(jù)封裝類
package metadataImpro;
public class Book {
private String bno;
private String bname;
private String bpublisher;
private String bauthor;
private String bdate;
private String bprice;
public String getBno() {
return bno;
}
public void setBno(String bno) {
this.bno = bno;
}
public String getBname() {
return bname;
}
public void setBname(String bname) {
this.bname = bname;
}
public String getBpublisher() {
return bpublisher;
}
public void setBpublisher(String bpublisher) {
this.bpublisher = bpublisher;
}
public String getBauthor() {
return bauthor;
}
public void setBauthor(String bauthor) {
this.bauthor = bauthor;
}
public String getBdate() {
return bdate;
}
public void setBdate(String bdate) {
this.bdate = bdate;
}
public String getBprice() {
return bprice;
}
public void setBprice(String bprice) {
this.bprice = bprice;
}
@Override
public String toString() {
return "Book [bno=" + bno + ", bname=" + bname + ", bpublisher=" + bpublisher + ", bauthor=" + bauthor
+ ", bdate=" + bdate + ", bprice=" + bprice + "]";
}
}
