JDBC连接常用数据库 基本的CURD

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;
	}