jdbc basic

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 ;
  
 }

}