package test;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class CRUD {
/**
* @param args
*/
public static void main(String[] args) {
// TODO Auto-generated method stub
try {
create();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
static void create() throws SQLException {
Connection conn = null;
Statement st = null;
PreparedStatement ps =null;//這種statement可以用來過濾資料庫查詢語句,防止SQL隱碼攻擊
ResultSet rs = null;
try {
conn = JdbcUtils.getConnection();
//3,Statement用於“運送”sql語句和sql語句執行結果
String paraSql = “select * from user hwere name=?”;
//對於上面這種帶?的sql可以用下面這個辦法來獲取statement
ps=conn.prepareStatement(sql);//這種建立連線的辦法雖然安全但是建立的時候比較耗時
String paraName = “’’ or 1 or ‘”;
ps.setString(1,paraName);
rs=ps.executeQuery();//這樣是查詢不到結果的,因為沒有一個使用者名稱叫做’’ or 1 or ‘
st = conn.createStatement();
String sql = "insert into user(name,birthday,money) values ('name1','1987-01-01','400')";
String sql1 = "update user set money=money+10";
String sql2 = "delete from user where id>5";
//4,執行sql
int count = st.executeUpdate(sql1);//增刪改全用這個,返回值是受影響的行數
rs = st.executeQuery("select * from user");
while(rs.next()) {
//也可以使用列名,使用列號要從1開始
//for(int i = 1)
int i = rs.getMetaData().getColumnCount(); //獲取列數
for(int j=1;j<=i;j++)
System.out.print(rs.getObject(j)+"\t");
System.out.println();
}
System.out.println(count);
} finally {
JdbcUtils.free(rs, st, conn);
}
}
}
package test;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
/* 簡陋工具類 */
public final class JdbcUtils {
private static String url="jdbc:mysql://localhost:3306/world";
private static String user = "root";
private static String password="mysql";
static {
try {
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
throw new ExceptionInInitializerError(e);
}
}
private JdbcUtils() {}
public static Connection getConnection() throws SQLException {
return DriverManager.getConnection(url,user,password);
}
public static void free(ResultSet rs,Statement st,Connection conn) {
try {
if (rs != null) {
rs.close();
}
}catch(SQLException e) {
e.printStackTrace();
}
finally {
try {
if (st != null) {
st.close();
}
} catch(SQLException e){
e.printStackTrace();
} finally {
if (conn != null)
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
}