JDBC连接各个数据库的className与url
JDBC连接DB2private 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;}
