jdbc Statement和PrepareStatement操作

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;


public class ConnHandle {
private static Connection conn=null;
private static Statement stmt=null;
private static PreparedStatement pstmt=null;
private static CallableStatement callstmt=null;
private static ResultSet rs=null;

//獲取資料庫連線
public static Connection getConn() {
String url="jdbc:mysql://127.0.0.1/dandan?user=root&password=dada&characterEncoding=utf-8";
try {
conn = DriverManager.getConnection(url);
} catch (SQLException e) {
e.printStackTrace();
}
return conn;
}

//執行方法
public static void main(String[] args) throws Exception {
//載入驅動
Class.forName("org.gjt.mm.mysql.Driver");
//獲取Connection
conn=ConnHandle.getConn();

/*******************************Statement handle**********************************************/
//獲取stmt
// stmt=conn.createStatement();

//normal insert
// String normal_insert="insert into student(name,sex,grade) values('dada','m','100000')";
// stmt.executeUpdate(normal_insert);

//normal delete
// String normal_delete="delete from student where name='dada'";
// stmt.executeUpdate(normal_delete);

//normal update
// String normal_update="update student set name='dada' where name in('hehe','tongtong')";
// stmt.executeUpdate(normal_update);

//normal query
// String view_sql="select * from student";
// rs=stmt.executeQuery(view_sql);
// ConnHandle.show(rs);
/*******************************Statement handle**********************************************/


//正常的資料庫操作是裡面所執行的方法是要傳遞引數的,因為它無法事先的建立有引數的查詢物件
//而Preparestatement則可以,所以它可以直接呼叫executeUpdate()裡面不用傳遞引數,因為上面
//已經把引數傳遞好了
/*******************************PrepareStatement handle**********************************************/
//PrepareStatement insert
// String pre_insert="insert into student(name,sex,grade) values (?,?,?)";
// pstmt=conn.prepareStatement(pre_insert);
// pstmt.setString(1, "dada1");
// pstmt.setString(2, "m");
// pstmt.setString(3, "1");
// pstmt.executeUpdate();

// //PrepareStatement delete
// String pre_delete="delete from student where name=?";
// pstmt=conn.prepareStatement(pre_delete);
// pstmt.setString(1, "dada1");
// pstmt.executeUpdate();

//PrepareStatement update
String pre_update="update student set name=? where name=?";
pstmt=conn.prepareStatement(pre_update);
pstmt.setString(1, "jack");
pstmt.setString(2, "dada");
pstmt.executeUpdate();


//PrepareStatement query
String pre_query="select * from student";
pstmt=conn.prepareStatement(pre_query);
rs=pstmt.executeQuery();
ConnHandle.show(rs);

ConnHandle.closeAll();
/*******************************PrepareStatement handle**********************************************/

}

//normal insert,delete,update
public int normal_update(String insert) {
try {
result = stmt.executeUpdate(insert);
} catch (SQLException e) {
e.printStackTrace();
}
return result;
}

//normal query
public ResultSet normal_query(String query) {
try {
rs=stmt.executeQuery(query);
} catch (SQLException e) {
e.printStackTrace();
}
return rs;

}

//列印結果集
public static void show(ResultSet rs) {
try {
while(rs.next()) {
System.out.println("Row: "+rs.getRow()+"\tId: "+rs.getInt(1)+"\tName: "+rs.getString(2)
+"\tSex: "+rs.getString(3)+"\tGrade: "+rs.getString(4));
}
} catch (SQLException e) {
e.printStackTrace();
}
}

//PrepareStatement date1 把傳遞過來的util型別的Date轉換為sql型別的date
    public static void setDate(int index,Date date) throws SQLException{
    pstmt.setDate(index, new java.sql.Date(date.getTime()));
    }
    //PrepareStatement date2 把String引數首先轉換為Date然後再轉換為sql型別Date
    public static void setDate(int index,String pattern,String string) throws Exception{
    SimpleDateFormat sdf = new SimpleDateFormat(pattern);
    Date date = sdf.parse(string);
    pstmt.setDate(index, new java.sql.Date(date.getTime()));
    }

//把關閉所有連線,寫成方法直接呼叫
public static void closeAll() throws Exception {
if(null!=conn) {
if(rs!=null) {
rs.close();
}
if(stmt!=null) {
stmt.close();
}
if(pstmt!=null) {
pstmt.close();
}
if(callstmt!=null) {
callstmt.close();
}
conn.close();
}
}
}