串連mysql資料庫相關語句

 

package com.test.database;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.logging.Logger;

public class DBConnect {
 static Connection conn = null;
 static Statement st = null;
 static ResultSet rs = null;

 public static Connection getConnection() throws SQLException {
  try {
   Class.forName("com.mysql.jdbc.Driver");
  } catch (ClassNotFoundException e) {
   Logger.getLogger(e.getMessage());
  }
  try {
   conn = DriverManager.getConnection(
     "Jdbc:mysql://localhost:3306/test", "root", "root");
  } catch (SQLException e) {
   e.getMessage();
   if (conn != null) {
    conn.close();
   }
  }
  return conn;
 }

 public static int getInsert(int sid, String sname, String sex)
   throws SQLException {
  conn = getConnection();
  int i = 0;
  String sql = "insert into student values("+sid+",'" + sname + "','" + sex
    + "')";
  /*
   * 這裡要注意細節上的錯誤，如果把sid寫成字串，在插入資料的時候會出現異常。
     而且在這個地方通常網上說的那種情況“資料庫裡序號沒有設定成自增”在這裡不適用。
  */
  try {
   st = conn.createStatement();
   i = st.executeUpdate(sql);
  } catch (Exception e) {
   if (conn != null)
    conn.close();
   e.printStackTrace();
  }
  return i;
 }

 public static String getSelect() throws SQLException {
  conn = getConnection();
  String sql = "select * from student";
  try {
   st = conn.createStatement();
   rs = st.executeQuery(sql);
   while (rs.next()) {
    rs.getInt("sid");
    rs.getString("sname");
    rs.getString("sex");
    System.out.println(rs.getInt("sid") + ","
      + rs.getString("sname") + "," + rs.getString("sex"));
   }
  } catch (SQLException e) {
   rs.close();
   if (conn != null)
    conn.close();
   e.printStackTrace();
  }
  return null;
 }

 public static int getUpdate(int sid, String sna, String se)
   throws SQLException {
  conn = getConnection();
  int i = 0;
  String sql = "update student set sname='" + sna + "',sex='" + se + "'";
  sql = sql + " where sid=" + sid;
  try {
   st = conn.createStatement();
   i = st.executeUpdate(sql);
  } catch (Exception e) {
   if (conn != null)
    conn.close();
   e.printStackTrace();
  }
  return i;
 }

 public static void getDelete(int sid) throws Exception {
  conn = getConnection();
  String sql = "delete from student where sid=" + sid;
  conn.createStatement().execute(sql);
  if (conn != null)
   conn.close();
 }

 public static void main(String args[]) throws Exception {
  DBConnect.getConnection();
//  DBConnect.getInsert(6, "werrr", "m");
  DBConnect.getUpdate(1, "1", "uii");
  DBConnect.getSelect();
  DBConnect.getDelete(0);
 }

}