package jdbc.test;
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 final class JdbcMySqlUtil {
private final String url = "jdbc:mysql://localhost:3306/testDB" ;
private final String user = "root" ;
private final String pwd ="sql";
private static JdbcMySqlUtil util = null;
private JdbcMySqlUtil() throws ClassNotFoundException {
Class.forName("com.mysql.jdbc.Driver");
}
public static JdbcMySqlUtil getInstance() throws ClassNotFoundException {
if (util == null) {
synchronized (JdbcMySqlUtil.class) {
if (util == null)
util = new JdbcMySqlUtil();
return util;
}
} else
return util;
}
public Connection getConnection() throws SQLException {
return DriverManager.getConnection(url, user, pwd);
}
public Statement getStatement() throws SQLException{
return this.getConnection().createStatement();
}
public PreparedStatement getPrepareStatement(String sql) throws SQLException{
return this.getConnection().prepareStatement(sql);
}
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 {
try {
if (conn != null)
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
}
package jdbc.test;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class CRUD {
public static int create() throws SQLException, ClassNotFoundException{
String str = "insert into user (name,address) values ('wang','fushan')";
Connection conn = JdbcMySqlUtil.getInstance().getConnection();
Statement st = conn.createStatement();
int x = st.executeUpdate(str);
JdbcMySqlUtil.free(null, st, conn);
return x ;
}
public static void read() throws SQLException, ClassNotFoundException{
//创建连接
Connection conn = JdbcMySqlUtil.getInstance().getConnection();
//生成sql语句
Statement state = conn.createStatement();
//执行语句
ResultSet rs = state.executeQuery("select * from user");
//使用数据
while(rs.next())
{
System.out.print(rs.getInt(1)+" " + rs.getString(2) +" " + rs.getString(3));
System.out.println();
}
//关闭连接
JdbcMySqlUtil.free(rs, state, conn);
}
public static int update() throws SQLException, ClassNotFoundException{
String str = "update user set name = 'Name'";
Connection conn = JdbcMySqlUtil.getInstance().getConnection();
Statement st = conn.createStatement();
int x = st.executeUpdate(str);
JdbcMySqlUtil.free(null, st, conn);
return x ;
}
public static int delete() throws SQLException, ClassNotFoundException{
String str = "delete from user where id > 2";
Connection conn = JdbcMySqlUtil.getInstance().getConnection();
Statement st = conn.createStatement();
int x = st.executeUpdate(str);
JdbcMySqlUtil.free(null, st, conn);
return x ;
}
}
package jdbc.test;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class PrepareCRUD {
public static int create(String name, String address) throws SQLException, ClassNotFoundException{
String sql = "insert into user (name,address) values (?,?)";
PreparedStatement ps = JdbcMySqlUtil.getInstance().getPrepareStatement(sql);
ps.setString(1, name);
ps.setString(2, address);
int x = ps.executeUpdate(); //PreparedStatement is a sub interface of Statement
// java.sql.Date is a sub class of java.util.Date
JdbcMySqlUtil.free(null, ps, ps.getConnection());
return x ;
}
}