前面剛介紹了怎麼連接數據庫，也寫了相應的模板，但是它的可維護性非常差，那麼怎麼解決這個問題呢？
首先寫一個配置文件jdbc.properties
## MySQL driver=com.mysql.jdbc.Driver url=jdbc:mysql://localhost:3306/hncu?useUnicode=true&characterEncoding=UTF-8 username=root password=1234 ## Oracle #driver=oracle.jdbc.driver.OracleDriver #url=jdbc:oracle:thin:@192.168.31.12:1521:orcl #username=scott #password=tiger
package cn.hncu.hibernate0;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.util.Properties;
public class ConnFactory {
private static Connection conn;
static {
try {
//讀取配置文件
Properties p = new Properties();
p.load(ConnFactory.class.getClassLoader().getResourceAsStream("jdbc.properties"));
String driver = p.getProperty("driver");
String url = p.getProperty("url");
String username = p.getProperty("username");
String pwd = p.getProperty("password");
Class.forName(driver);
conn = DriverManager.getConnection(url,username,pwd);
System.out.println("已連接到數據庫..."+conn);
} catch (Exception e) {
throw new RuntimeException("讀取配置文件失敗", e);
}
}
public static Connection getConn(){
return conn;
}
public static void main(String[] args) {
getConn();
}
}
最後直接通過ConnFactory.getConn()獲得。這要做的好處，當改變所要連接的數據庫類型時，只需要修改配置文件中的內容即可。
@Test
public void getXXXDemo() throws Exception{
Statement st = ConnFactory.getConn().createStatement();
String sql = "select * from book";
ResultSet rs = st.executeQuery(sql);
while(rs.next()){
Integer id = rs.getInt(1);//這裡的1表示數據表中的第一列，下面同理
String name = rs.getString(2);
//Double price = (Double)rs.getObject(3);//出異常，因為內部是采用BigDecimal來處理
Double price = rs.getDouble(3);
Object dateTime = rs.getObject(4);//把日期和時間作為一個整體讀取出來
System.out.println(id+","+name+","+price+","+dateTime);
String strDateTime = dateTime.toString();
System.out.println(strDateTime);
strDateTime = rs.getDate(4)+"--"+rs.getTime(4);//日期和時間可以單獨獲取
System.out.println(":::"+strDateTime);
}
ConnFactory.getConn().close();
}
注：對於自動增長列，刪除之後再插入新記錄，序號不會回頭，繼續往前增長。即中間會出現空號
1、executeQuery: 只能執行select語句
2、executeUpdate: 可以執行insert、delete和update語句，但不能執行select
3、execute:增刪改查的4種(任意)語句都能執行。該方法若執行非select語句時返回false，執行select語句時返回true，且st對象會緩存該次查詢的結果，我
們可通過ResultSet rs = st.getResultSet()來獲得結果集
@Test
public void executeXXXDemo() throws Exception{
Statement st = ConnFactory.getConn().createStatement();
String sql = "select * from book";
//String sql = "insert into book(name,price,pub) values('軟件工程',22.35,'2015-12-05 22:12:23')";
//String sql = "update book set price=38.88 where name='軟件工程'";
//String sql = "delete from book where name='軟件工程'";
//st.executeQuery(sql);
//st.executeUpdate(sql);
boolean boo = st.execute(sql);
if(boo){
ResultSet rs = st.getResultSet();
while(rs.next()){
System.out.println(rs.getObject(2));
}
}
}
案例、用戶登錄（通過用戶輸入信息來拼接sql語句----很危險）
@Test//用戶登錄
public void login() throws Exception{
Connection con = ConnFactory.getConn();
Statement st = con.createStatement();
Scanner sc = new Scanner(System.in);
int id = sc.nextInt(); sc.nextLine();
String name = sc.nextLine();
String sql = "select count(*) from stud where id="+id+" and sname='"+name+"'";
System.out.println("sql:"+sql);
ResultSet rs = st.executeQuery(sql);
rs.next();
int a = rs.getInt(1);
if(a<=0){
System.out.println("登錄不成功");
}else{
System.out.println("登錄成功");
}
con.close();
}
黑的方法，輸入：1002(回車) 1' or '1'='1
況，應該用PreparedStatement來解決！
@Test//用戶登錄 黑：1002(回車) 1' or '1'='1
public void login2() throws Exception{
Scanner sc = new Scanner(System.in);
Connection con = ConnFactory.getConn();
String sql = "select count(*) from stud where id=? and sname=?";//需要用戶輸入的地方，用占位符('?')來代替，然後在後續通過設參來給占位符賦值
PreparedStatement pst = con.prepareStatement(sql);
//設置參數
int id = sc.nextInt(); sc.nextLine();
pst.setInt(1, id); //參數1----代表第1個占位符
String name = sc.nextLine();
pst.setString(2, name);//參數2
ResultSet rs = pst.executeQuery();
rs.next();
int a = rs.getInt(1);
if(a<=0){
System.out.println("登錄不成功");
}else{
System.out.println("登錄成功");
}
con.close();
}
@Test //演示獲取自動增長列如id
public void saveAuto() throws Exception{
Connection con = ConnFactory.getConn();
String sql = "insert into book(name,price,pub) values('JavaEE',100.8,'2013-06-12 08:30:30')";
Statement st = con.createStatement();
//st.executeUpdate(sql);
st.executeUpdate(sql,Statement.RETURN_GENERATED_KEYS);
ResultSet rs = st.getGeneratedKeys();//裡面封裝了自動生成的所有值
if(rs.next()){
int id = rs.getInt(1);//獲取第1個自動增長列
System.out.println("自動增長的id:"+id);
}
System.out.println("-----------------");
//預處理語句
sql = "insert into book(name,price) values(?,?)";
PreparedStatement pst = con.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
pst.setString(1, "計算機基礎");
pst.setDouble(2, 28);
pst.executeUpdate();
ResultSet rs2 = pst.getGeneratedKeys();//裡面封裝了自動生成的所有值
if(rs2.next()){
int id = rs2.getInt(1);//獲取第1個自動增長列
System.out.println("自動增長的id:"+id);
}
}
public void batch() throws Exception{
Connection con = ConnFactory.getConn();
String sql = "insert into book(name,price,pub) values('JavaEE',100.8,'2015-06-12 08:30:30')";
Statement st = con.createStatement();
for(int i=0;i<10;i++){
st.addBatch(sql);
}
sql = "update book set price=price*1.1 where price>100";
st.addBatch(sql);
int[] a = st.executeBatch();
for(int r:a){
System.out.println(r);//r為每條sql語句所影響的記錄數
}
}
public void preparedBatch() throws Exception{
Connection con = ConnFactory.getConn();
String sql = "insert into book(name,price,pub) values(?,?,?)";
PreparedStatement pst = con.prepareStatement(sql);
for(int i=0;i<5;i++){
pst.setString(1, "Java"+i);
pst.setDouble(2, 55.85+i);
pst.setString(3, "2016-12-10 07:07:08");
pst.addBatch(); //pst的方式不能帶參數sql
}
//pst.executeBatch();
int[] a = pst.executeBatch();
for(int r:a){
System.out.println(r);//r為每條sql語句所影響的記錄數
}
}
