Java JDBC实现数据库的增删改查操作

主要有三部分组成：一：连接驱动。   二：Model对象。  三：实现类

 

连接驱动：这里连接的是sybase数据库。不同数据库请添加相应的驱动以及连接。

 

package testJDBC;

import java.sql.Connection;

import java.sql.DriverManager;

public class DataBaseConn {

public static Connection getDbConn() throws Exception {

Connection conn = null;

try {

Class.forName("com.sybase.jdbc3.jdbc.SybDriver");

} catch (Exception e) {

// TODO Auto-generated catch block

System.out.println("Can not find DataBase  驱动！");

e.printStackTrace();

}

              

try {

conn = DriverManager.getConnection(

"jdbc:sybase:Tds:***.***.***.***:端口号/DB名称", "用户名",

"密码");

} catch (Exception e) {

// TODO Auto-generated catch block

System.out.println("Database Connect Errror  !!!");

e.printStackTrace();

}

return conn;

}

}

 

 

model对象：

 

 

package testJDBC;

import java.io.Serializable;

import java.util.Date;

/**

 * @author fl

 */

@SuppressWarnings("serial")

public class NoteModel implements Serializable{ 

private Long id;

private String ip; 

private String visiturl;

private String param;

private Date visittime;

// 这里的get()、set()方法

}

 

 

实现类：这里有个需要注意的就是对时间的处理。java.sql.Date 和 java.util.Date是不相同的。

 

 

 

 java.sql.Date  只是用于SQl语句，只有日期没有时间。

   java.util.Date 用于除SQl语句外的所有日期处理。

 

  java.util.Date 是 java.sql.Date 的父类。

 

  转化：

  java.sql.Date sDate = new java.sql.Date();

  java.util.Date date = new java.util.Date(dDate.getTime(0);

 

 

-------------------------------------------------------------------------------------------------------

 

 

package testJDBC;

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.util.ArrayList;

import java.util.Date;

import java.util.List;

public class operClass {

//添加数据

public static boolean createNote(NoteModel nm,String tableName){

PreparedStatement ps = null;

String sql = "insert into "+tableName+"(IP,VISITURL,PARAM,VISITTIME) values (?,?,?,?)";

Connection conn = null;

try {

conn = DataBaseConn.getDbConn();

ps = conn.prepareStatement(sql);

ps.setString(1,nm.getIp());

ps.setString(2,nm.getVisiturl());

ps.setString(3,nm.getParam());

ps.setDate(4,(java.sql.Date) nm.getVisittime());

ps.execute();

ps.close();

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

} catch (Exception e) {

// TODO Auto-generated catch block

e.printStackTrace();

}finally{

try {

conn.close();

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

}

return true;

}

//修改数据

public static boolean updateNote(NoteModel nm,String tableName){

//这样携带参数，注意引号附近的空格。

String sql = "update "+tableName+" SET PARAM=?, IP=? where ID = ?";

Connection conn= null;

PreparedStatement ps = null;

try {

conn = DataBaseConn.getDbConn();

ps = conn.prepareStatement(sql);

ps.setString(1,nm.getParam());

ps.setString(2,nm.getIp());

ps.setLong(3,nm.getId());

ps.executeUpdate();

ps.close();

} catch (Exception e) {

// TODO Auto-generated catch block

e.printStackTrace();

}finally{

try {

conn.close();

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

}

return true;

}

//删除数据

public static boolean delete(NoteModel nm,String tableName){

//利用?作为占位符

String sql="delete from "+tableName+" where ID=?";

//1:首先声明 连接对象

Connection conn = null;

//2:声明预编译的PreparedStatement对象

PreparedStatement ps = null;

try {

//3:设置连接对象参数

conn = DataBaseConn.getDbConn();

//4: 设置预编译的Sql语句

ps=conn.prepareStatement(sql);

ps.setLong(1,nm.getId());

//5:执行

ps.executeUpdate();

// 6：关闭

ps.close();

} catch (Exception e) {

// TODO Auto-generated catch block

e.printStackTrace();

}finally{

try {

conn.close();

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

}

return true;

}

//按照主键查询

public static NoteModel queryById(NoteModel nm,String tableName){

NoteModel getNm = new NoteModel();

String sql = "select ID,IP,VISITURL,PARAM,VISITTIME from "+tableName+" where ID=?";

Connection conn = null;

PreparedStatement ps = null;

try {

conn = DataBaseConn.getDbConn();

ps = conn.prepareStatement(sql);

ps.setLong(1,nm.getId());

//取得返回值.  游标

ResultSet rs =ps.executeQuery();

if(rs.next()){

getNm.setId(rs.getLong(1));

getNm.setIp(rs.getString(2));

getNm.setVisiturl(rs.getString(3));

getNm.setParam(rs.getString(4));

getNm.setVisittime(rs.getDate(5));

}

//关闭 

rs.close();

ps.close();

} catch (Exception e) {

// TODO Auto-generated catch block

e.printStackTrace();

}finally{

try {

conn.close();

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

}

return getNm;

}

//查询全部数据

public static List queryList(String tableName){

List list = new ArrayList();

String sql = "select ID,IP,VISITURL,PARAM,VISITTIME from "+tableName;

Connection conn = null;

PreparedStatement ps = null;

try {

conn = DataBaseConn.getDbConn();

ps = conn.prepareStatement(sql);

// 返回结果集

ResultSet rs = ps.executeQuery();

while(rs.next()){

NoteModel getNm = new NoteModel();

getNm.setId(rs.getLong(1));

getNm.setIp(rs.getString(2));

getNm.setVisiturl(rs.getString(3));

getNm.setParam(rs.getString(4));

getNm.setVisittime(rs.getDate(5));

list.add(getNm);

}

rs.close();

ps.close();

} catch (Exception e) {

// TODO Auto-generated catch block

e.printStackTrace();

}finally{

try {

conn.close();

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

}

return list;

}

//按照条件查询

public static List queryByOCndtion(NoteModel nm,String tableName){

List list = new ArrayList();

String sql = "select ID,IP,VISITURL,PARAM,VISITTIME from "+tableName+" where 1=1 ";

Connection conn = null;

PreparedStatement ps = null;

try {

conn = DataBaseConn.getDbConn();

//这里因为是做的动态查询，所以要做一个通用 的Sql语句的拼接 

ps = conn.prepareStatement(getSql(nm,sql));

//设置参数值

setPrepredStatement(ps,nm);

ResultSet rs = ps.executeQuery();

while(rs.next()){

NoteModel getNm = new NoteModel();

getNm.setId(rs.getLong(1));

getNm.setIp(rs.getString(2));

getNm.setVisiturl(rs.getString(3));

getNm.setParam(rs.getString(4));

getNm.setVisittime(rs.getDate(5));

list.add(getNm);

}

rs.close();

ps.close();

} catch (Exception e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

return list;

}

//拼接SQL语句

public static String getSql(NoteModel nm,String sql){

if(nm.getIp() != null && nm.getIp().trim().length() >0){

sql+="and IP = ? ";

}

if(nm.getParam() != null && nm.getParam().trim().length() >0){

sql+="and PARAM like ? ";

}

if(nm.getVisiturl() != null && nm.getVisiturl().trim().length() > 0){

sql+="and VISITURL like ? ";

}

System.out.println("sql: "+sql);

return sql;

}

//确定参数位置

public static PreparedStatement setPrepredStatement(PreparedStatement ps,NoteModel nm)throws Exception{

int count = 1;

if(nm.getIp() != null && nm.getIp().trim().length() >0){

ps.setString(count,nm.getIp());

count ++;

}

if(nm.getParam() != null && nm.getParam().trim().length() >0){

// 百分号一定要拼接在这里，整体作为一个参数。

ps.setString(count,"%"+nm.getParam()+"%");

count ++;

}

if(nm.getVisiturl() != null && nm.getVisiturl().trim().length() > 0){

ps.setString(count,"%"+nm.getVisiturl()+"%");

count ++;

}

return ps;

}

//测试函数

public static void main(String[] args) {

String tableName="Record_UserVisit20100821";

NoteModel nmM = new NoteModel();

nmM.setIp("789.789.789.789");

nmM.setParam("asdf");

nmM.setVisiturl("qwer");

List list=queryByOCndtion(nmM,tableName);

System.out.println(list.size());

for(int i =0;i<list.size();i++){

NoteModel nm = (NoteModel) list.get(i);

System.out.println("ID:"+nm.getId()+", IP:"+nm.getIp()+", param:"+nm.getParam()+", data:"+nm.getVisittime()+", url:"+nm.getVisiturl());

}

NoteModel nm = new NoteModel();

nm.setIp("789.456.123.963");

nm.setParam("name=2B&sex=m");

nm.setVisiturl("www.b2bjoy.com");

Date date = new Date();

java.sql.Date sDate = new java.sql.Date(date.getTime());

nm.setVisittime(sDate);

}

}