JDBC MySQL

加入mysql-connector-java-5.1.21-bin.jar

package neo.mysql;

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

public class JdbcMySQL {
 Connection conn;
 String username;
 String password;
 String url;
 String databaseName;
 String port = "3306";
 String sqlstr = "jdbc:mysql://"+url+":"+port+"/"+databaseName+"?useUnicode=true&characterEncoding=UTF-8";
 String userNameString = "name";
 String passwdNameString = "passwd";
 public JdbcMySQL(String url, String databaseName, String username, String password){
  sqlstr = "jdbc:mysql://"+url+":"+port+"/"+databaseName+"?useUnicode=true&characterEncoding=UTF-8";
  //透過java.lang.Class類別的forName()來載入並向DriverManager註冊JDBC驅動程式（驅動程式會自動透過DriverManager.registerDriver()方法註冊）， MySQL的驅動程式類別是com.mysql.jdbc.Driver
   try
       {
         Class.forName("com.mysql.jdbc.Driver").newInstance();
       //jdbc:mysql://主機名稱:連接埠/資料庫名稱?參數1=值1&參數2=值2
      conn = java.sql.DriverManager.getConnection(sqlstr,username,password);
   if(!conn.isClosed()) System.out.println("MySQL資料庫連線成功"); 
  } catch (SQLException e) {
   System.out.println("連不上MySQL!!!"); 
  } catch (InstantiationException e) {
   e.printStackTrace();
  } catch (IllegalAccessException e) {
   e.printStackTrace();
  } catch (ClassNotFoundException e) {
   e.printStackTrace();
  }
 }
 public void getConnection(){
   try
       {
         Class.forName("com.mysql.jdbc.Driver").newInstance();
       //jdbc:mysql://主機名稱:連接埠/資料庫名稱?參數1=值1&參數2=值2
      conn = java.sql.DriverManager.getConnection(sqlstr,username,password);
   if(!conn.isClosed()) System.out.println("MySQL資料庫連線成功"); 
  } catch (SQLException e) {
   System.out.println("連不上MySQL!!!"); 
  } catch (InstantiationException e) {
   e.printStackTrace();
  } catch (IllegalAccessException e) {
   e.printStackTrace();
  } catch (ClassNotFoundException e) {
   e.printStackTrace();
  }
 }
 public void close(){
  try {
   conn.close();
  } catch (SQLException e) {
   System.out.println("無法關閉跟MySQL的連線!!!"); 
  }
 }
 public void createTable(){
  String createdbSQL = "CREATE TABLE User (" +
     "    id     INTEGER " +
     "  , name    VARCHAR(20) " +
     "  , passwd  VARCHAR(20))";
  try {
   conn.setAutoCommit(false);
   Statement state = conn.createStatement();
   state.executeUpdate(createdbSQL);
   conn.commit();
  } catch (SQLException e) {
   System.out.println("MySQL新建資料表失敗!!!"); 
   e.printStackTrace();
  } 
 }
 public void insertData(String name, String passwd){
  String insertdbSQL = "insert into User(id,name,passwd) " +
       "select ifNULL(max(id),0)+1,?,? FROM User";
  try {
   conn.setAutoCommit(false);
   PreparedStatement pst = conn.prepareStatement(insertdbSQL);
   pst.setString(1, name);
   pst.setString(2, passwd);
   pst.executeUpdate();
   conn.commit();
  } catch (SQLException e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
  }
 }
 public void updateData(int id,String name, String passwd){
  String insertdbSQL = "update User set name = ? ,passwd = ? where id = ?";
  try {
   conn.setAutoCommit(false);
   PreparedStatement pst = conn.prepareStatement(insertdbSQL);
   pst.setInt(3, id);
   pst.setString(1, name);
   pst.setString(2, passwd);
   pst.executeUpdate();
   conn.commit();
  } catch (SQLException e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
  }
 }
 public void delete(String tableName,String username){
  String sql = "delete from "+tableName+" where "+userNameString+" = ?";
  Statement state;
  try {
   conn.setAutoCommit(false);
   PreparedStatement pst = conn.prepareStatement(sql);
   pst.setString(1, username);
   pst.execute();
   conn.commit();
   System.out.println("刪除成功!!!");   
  } catch (SQLException e) {
   // TODO Auto-generated catch block
   System.out.println("刪除失敗!!!"); 
   e.printStackTrace();
  }
 }
 public void research(){
  String sql = "select * from User";
  Statement state;
  try {
   conn.setAutoCommit(false);
   state = conn.createStatement();
   ResultSet rs = state.executeQuery(sql);
   conn.commit();
   while (rs.next()) {
    System.out.println(rs.getInt("id"));
    System.out.println(rs.getString("name"));
    System.out.println(rs.getString("passwd"));  
     //System.out.println(rs.toString());
    }
  } catch (SQLException e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
  }
 }
 public boolean validation(String tableName,String username,String passwd){
  String sql = "SELECT "+passwdNameString+" FROM `"+tableName+"` WHERE "+userNameString+"=?";
  try {
   conn.setAutoCommit(false);
   PreparedStatement pst = conn.prepareStatement(sql);
   pst.setString(1, username);
   ResultSet rs = pst.executeQuery();
   conn.commit();
   while (rs.next()) {
    if (rs.getString(passwdNameString).equalsIgnoreCase(passwd)) return true;
   }
   return false;
  } catch (SQLException e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
  }
  return false;
  
 }
}