java jdbc與odbc資料庫的串連mysql資料庫

標籤：

1.jdbc串連資料庫要添加mysql-connector-java-5.1.38-bin.jar庫

2.odbc串連資料庫要添加odbc資料來源

3.測試代碼

DBHelper.java代碼jdbc串連資料庫

import java.beans.Statement;
import java.sql.DriverManager;
import com.mysql.jdbc.Connection;
import com.mysql.jdbc.PreparedStatement;
import com.sun.jndi.url.corbaname.corbanameURLContextFactory;

public class DBHelper {
public final String url = "jdbc:mysql://127.0.0.1:3306/School?useUnicode=true&characterEncoding=GBK";
public final String username = "root";//使用者名稱
public final String name = "com.mysql.jdbc.Driver";
public final String pswd = null;//密碼
public Connection conn = null;//串連
public PreparedStatement pst = null;//準備執行的sql語句
public DBHelper(String sql) {
try {

Class.forName(name);
conn = (Connection) DriverManager.getConnection(url, username, pswd);
pst = (PreparedStatement) conn.prepareStatement(sql);
System.out.println("jdbc連結資料庫");
} catch (Exception e) {

System.out.println("載入驅動失敗" + e.toString());
}

}

public PreparedStatement DBQ() {

return pst;
}
public void CloseDB() {
try {
conn.close();
pst.close();
} catch (Exception e) {

System.out.println("出現異常");
}

}

}

DBHelper1.java代碼，ODBC串連資料庫

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;

public class DBHelper1 {
public final String url="jdbc:odbc:TEST";
public final String name = "root";
public final String psd = null;
public final String name1 = "sun.jdbc.odbc.JdbcOdbcDriver";
public Connection conn1 = null;
public PreparedStatement pst = null;

public DBHelper1(String sql) {
try {

Class.forName(name1);
conn1 = (Connection) DriverManager.getConnection(url,
name, psd);
pst = (PreparedStatement) conn1.prepareStatement(sql);
System.out.println("ODBC連結資料庫！");
} catch (Exception e) {

System.out.println("載入驅動失敗" + e.toString());
}

}

public PreparedStatement DBQ() {

return pst;
}

public void CloseDB() {
try {

conn1.close();
pst.close();
} catch (Exception e) {

System.out.println("出現異常");
}

}

}

程式的入口：MainUI.java實現程式的增刪改查
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Set;

 

public class MainUI {

public static void main(String[] args) {
String name=null;
String age=null;
String id=null;
String sql="select *from teacher";
String sql1="insert into teacher values(?,?,?)";
String sql2="update teacher set age=age+1 where tid=?";
String sql3="delete from teacher where tid=?";
PreparedStatement pst=null;
try {
//************************************************************************************************//
//查詢資訊
//************************************************************************************************//
   DBHelper dbHelper=new DBHelper(sql);//產生資料庫物件準備執行sql語句
   pst=dbHelper.DBQ();//返回對象pst
   pst.setString(1, "1234");//為預留位置賦值
ResultSet ret=pst.executeQuery();//執行sql語句擷取結果集
   while(ret.next())//取出結果集
{
id=ret.getString(1);
name=ret.getString(2);
    age=ret.getString(3);
System.out.println(id+","+name+","+age);
}
   dbHelper.CloseDB();





//************************************************************************************************//
//插入資訊
//************************************************************************************************//
   DBHelper dbHelper1=new DBHelper(sql1);
pst=dbHelper1.DBQ();
pst.setString(1, "1239");
pst.setString(2, "王二");
   pst.setString(3, "54");
   int f=pst.executeUpdate();
   System.out.println(f);
   if(f!=0)
   {
System.out.println("插入記錄成功！");

}
else {
    
    System.out.println("插入記錄失敗");
   }
dbHelper1.CloseDB();




//************************************************************************************************//
//修改資訊
//************************************************************************************************//
   
   DBHelper dbHelper2=new DBHelper(sql2);
   pst=dbHelper2.DBQ();
   pst.setString(1, "1234");
   int t=pst.executeUpdate();
   if(t!=0)
   {
    System.out.println("更新成功！");
   }
   else {
    System.out.println("更新失敗！");
   }
   dbHelper2.CloseDB();

//**********************************************************************************************//
//刪除資訊
//********************************************************************************************//
DBHelper1 dbHelper3=new DBHelper1(sql3);
pst=dbHelper3.DBQ();
pst.setString(1, "1239");
int d=pst.executeUpdate();
if(d!=0)
{
System.out.println("刪除成功！");

}
else {
System.out.println("刪除失敗！");
}
dbHelper3.CloseDB();

} catch (Exception e) {
System.out.println(e.toString());
}

}

}

java jdbc與odbc資料庫的串連mysql資料庫