JDBC連接各個數據庫的className與url
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";
JDBC連接MySQL(mm.mysql-3.0.2-bin.jar)
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";
JDBC連接Oracle(classes12.jar)
private String className="oracle.jdbc.driver.OracleDriver";
private String url="jdbc:oracle:thin:@localhost:1521:lwc"; |
JDBC連接數據庫案例
package com.itlwc; import java.sql.Connection;
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) {
System.out.println("連接數據庫失敗");
}
} 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) {
try {
state.close();
} catch (SQLException e) {
e.printStackTrace();
}
state = null;
}
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
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(?,?,?,?,?)";
PreparedStatement ps = null;
try {
ps = conn.prepareStatement(sql);
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());
ps.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}
DBConnection.close(null, ps, conn);
} |
刪除方法
使用拼sql刪除
public void delete1(String id) {
String sql = "delete from student where id='" + id+"'";
PreparedStatement ps = null;
try {
ps = conn.prepareStatement(sql);
ps.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}
DBConnection.close(null, ps, conn);
}
使用替換變量刪除
public void delete2(String id) {
String sql = "delete from student where id=?";
PreparedStatement ps = null;
try {
ps = conn.prepareStatement(sql);
ps.setString(1, id);
ps.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}
DBConnection.close(null, ps, conn);
} |
修改方法
使用拼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();
PreparedStatement ps = null;
try {
ps = conn.prepareStatement(sql);
ps.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}
DBConnection.close(null, ps, conn);
}
使用替換變量修改
public void update2(Student student) {
String sql = "update student set code=?,name=?,sex=?,age=? where id=?";
PreparedStatement ps = null;
try {
ps = conn.prepareStatement(sql);
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());
ps.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}
DBConnection.close(null, ps, conn);
} |
查詢方法
查詢得到一個對象
public Student findById(int id) {
String sql = "select * from student where id=" + id;
Student student = new Student();
PreparedStatement ps = null;
ResultSet rs = null;
try {
ps = conn.prepareStatement(sql);
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));
}
} catch (SQLException e) {
e.printStackTrace();
}
DBConnection.close(rs, ps, conn);
return student;
}
查詢得到一組數據
@SuppressWarnings("unchecked")
public List find() {
String sql = "select * from student";
List list = new ArrayList();
PreparedStatement ps = null;
ResultSet rs = null;
try {
ps = conn.prepareStatement(sql);
rs = ps.executeQuery();
while (rs.next()) {
Student student = new Student();
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));
list.add(student);
}
} catch (SQLException e) {
e.printStackTrace();
}
DBConnection.close(rs, ps, conn);
return list;
} |
統計數據庫總條數
public int getRows() {
int totalRows = 0;
String sql = "select count(*) as totalRows from student";
PreparedStatement ps = null;
ResultSet rs = null;
try {
ps = conn.prepareStatement(sql);
rs = ps.executeQuery();
if (rs.next()) {
totalRows = Integer.valueOf(rs.getString("totalRows"));
}
} catch (SQLException e) {
e.printStackTrace();
}
DBConnection.close(rs, ps, conn);
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;
} |