JDBC連接配接常用資料庫 基本的CURD

jdbc連接配接db2

 private string classname="com.ibm.db2.jdbc.net.db2driver";

   private string url="jdbc:db2://localhost:8080/lwc";

jdbc連接配接microsoft sqlserver(microsoft)

 private string classname="com.microsoft.jdbc.sqlserver.sqlserverdriver";

 private string url="jdbc:microsoft:sqlserver://

  localhost:1433;selectmethod=cursor;databasename=lwc";

jdbc連接配接sybase(jconn2.jar)

 private string classname="com.sybase.jdbc2.jdbc.sybdriver";

   private string url="jdbc:sybase:tds:localhost:2638";

   private string classname="org.gjt.mm.mysql.driver";

   private string url="jdbc:mysql://localhost:3306/lwc";

jdbc連接配接postgresql(pgjdbc2.jar)

   private string classname="org.postgresql.driver";

   private string url="jdbc:postgresql://localhost/lwc";

   private string classname="oracle.jdbc.driver.oracledriver";

   private string url="jdbc:oracle:thin:@localhost:1521:lwc";

　　jdbc連接配接資料庫案例

package com.itlwc;

import java.sql.drivermanager;

import java.sql.resultset;

import java.sql.sqlexception;

import java.sql.statement;

public class dbconnection {

 private static connection conn = null;

 private string user = "";

 private string password = "";

 private string classname = "com.microsoft.jdbc.sqlserver.sqlserverdriver";

 private string url = "jdbc:microsoft:sqlserver://"

   + "localhost:1433;selectmethod=cursor;databasename=lwc";

 private dbconnection() {

  try {

   class.forname(this.classname);

   conn = drivermanager.getconnection(url, user, password);

   system.out.println("連接配接資料庫成功");

  } catch (classnotfoundexception e) {

   system.out.println("連接配接資料庫失敗");

  } catch (sqlexception e) {

  }

 }

 public static connection getconn() {

  if (conn == null) {

   conn = (connection) new dbconnection();

  return conn;

 // 關閉資料庫

 public static void close(resultset rs, statement state, connection conn) {

  if (rs != null) {

   try {

    rs.close();

   } catch (sqlexception e) {

    e.printstacktrace();

   }

   rs = null;

  if (state != null) {

    state.close();

   state = null;

  if (conn != null) {

    conn.close();

   conn = null;

 public static void main(string[] args) {

  getconn();

}

　基本curd

擷取資料庫連接配接請查考jdbc連接配接常用資料庫

private connection conn = dbconnection.getconn();

　　增加方法

使用拼sql增加

public void add1(student student) {

 string sql = "insert into student values(" + student.getid() + ",'"

   + student.getcode() + "','" + student.getname() + "',"

   + student.getsex() + "," + student.getage() + ")";

 preparedstatement ps = null;

 try {

  ps = conn.preparestatement(sql);

  ps.executeupdate();

 } catch (sqlexception e) {

  e.printstacktrace();

 dbconnection.close(null, ps, conn);

使用替換變量增加

public void add2(student student) {

 string sql = "insert into student values(?,?,?,?,?)";

  ps.setstring(1, student.getcode());

  ps.setstring(2, student.getname());

  ps.setstring(3, student.getsex());

  ps.setstring(4, student.getage());

  ps.setstring(5, student.getid());

　　删除方法

使用拼sql删除

public void delete1(string id) {

 string sql = "delete from student where id='" + id+"'";

使用替換變量删除

public void delete2(string id) {

 string sql = "delete from student where id=?";

  ps.setstring(1, id);

　修改方法

使用拼sql修改

public void update1(student student) {

 string sql = "update student set code='" + student.getcode()

   + "',name='" + student.getname() + "',sex=" + student.getsex()

   + ",age=" + student.getage() + " where id=" + student.getid();

使用替換變量修改

public void update2(student student) {

 string sql = "update student set code=?,name=?,sex=?,age=? where id=?";

　　查詢方法

查詢得到一個對象

public student findbyid(int id) {

 string sql = "select * from student where id=" + id;

 student student = new student();

 resultset rs = null;

  rs = ps.executequery();

  if (rs.next()) {

   student.setid(rs.getstring(1));

   student.setcode(rs.getstring(2));

   student.setname(rs.getstring(3));

   student.setsex(rs.getstring(4));

   student.setage(rs.getstring(5));

 dbconnection.close(rs, ps, conn);

 return student;

查詢得到一組資料

@suppresswarnings("unchecked")

public list find() {

 string sql = "select * from student";

 list list = new arraylist();

  while (rs.next()) {

   student student = new student();

   list.add(student);

 return list;

　統計資料庫總條數

public int getrows() {

 int totalrows = 0;

 string sql = "select count(*) as totalrows from student";

   totalrows = integer.valueof(rs.getstring("totalrows"));

 return totalrows;

　　執行存儲過程

第一種

 public string retrieveid(string tablename,string interval) throws sqlexception {  

     connection conn = dbconnection.getconn();  

     string sql = "exec p_xt_idbuilder '" + tablename + "','" + interval+ "'";  

     preparedstatement ps = conn.preparestatement(sql);

  resultset rs = ps.executequery();

     string maxid = "";  

     if(rs.next()){  

         maxid = rs.getstring("bh");  

     }  

     dbconnection.close(rs, ps, conn);  

     return maxid;  

第二種

 public string retrieveid(string tablename,string interval) throws sqlexception {

  connection conn = dbconnection.getconn();

  callablestatement cs = conn.preparecall("{call p_xt_idbuilder(?,?,?)}");

     cs.setstring(1, tablename);

     cs.setstring(2, interval);

     cs.registeroutparameter(3,java.sql.types.varchar);

     cs.executeupdate();

     string maxid = "";

     maxid=cs.getstring(3);   

  dbconnection.close(null, cs, conn);

  return maxid;

最新内容請見作者的github頁：http://qaseven.github.io/