实现对SQLServer和MySql数据库通用链接及数据的增删改
我们经常需要和数据库打交道,对数据库数据进行增改删查的操作,首先我们必须要先链接数据库,然后对数据内容进行相关增删改操作。
首先看一下目录结构
程序解读:一共有三个子包分别为model包test包和util包
@:model包是数据信息的类
@@:test包是对数据进行查询获取内容
@@@:util包里的DBUtil是对数据库进行链接和增删改操作的内容,info文件是数据库的相关信息包括,url,name和pwd等
下面向大家展示代码
util包的DBUtil.java
package com.util;
import java.io.IOException;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;
/*
* 通用增删改!!!!!!!!!!!!!!
* 数据库驱动
* 张叔行
*/
public class DBUtil {
public DBUtil() {System.out.println("启动jdbc");}
/**
* 定义变量
*/
private static String driver = null;//驱动
private static String url = null;//mysql链接
private static String name = null;//数据库链接账户
private static String pwd = null;//数据库链接密码
/**
* 定义静态代码块
*/
static {
Properties porperties = new Properties();//java 的配置文件
try {
porperties.load(DBUtil.class.getResourceAsStream("info.properties"));
driver = porperties.getProperty("driver");
url = porperties.getProperty("url");
name = porperties.getProperty("name");
pwd = porperties.getProperty("pwd");
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
/**
* 链接数据库
*/
public static Connection getConn() {
Connection conn = null;
try {
Driver.class.forName(driver);
conn = DriverManager.getConnection(url,name,pwd);
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
System.out.println("数据库链接成功");
return conn;
}
/**
* 关闭数据库
*/
public static void setClose(Connection conn,PreparedStatement ps , ResultSet rs) {
try {
if(conn != null) {
conn.close();
}else if(ps != null) {
ps.close();
}else if(rs != null) {
rs.close();
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
/**
* 通用增删改
*/
public static boolean executeUpdate(String sql , Object[] obj) {
PreparedStatement ps = null;
Connection conn = getConn();
Boolean bool = false;
int rsInt = 0;
try {
sql = "insert into user(u_id,u_name,u_sex,u_phone,u_money,u_map) values(?,?,?,?,?,?)";
ps = conn.prepareStatement(sql);
for(int i = 0;i<obj.length;i++) {
ps.setObject(i+1, obj[i]);
}
rsInt = ps.executeUpdate();
if(rsInt < 0) {
System.out.println("添加失败");
bool = false;
}else {
System.out.println("添加成功");
bool = true;
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return bool;
}
/**
* 通用查通用查不能用list指定的返回类型要是通用的ResultSet来返回
* 通用指的是任何查询都能用
*/
public static ResultSet executeSelect(String sql , Object[] obj) {
Connection conn = getConn();
PreparedStatement ps = null;
ResultSet rs = null;
try {
ps = conn.prepareStatement(sql);
//如果object的值不等于空来执行这一句话
if( obj != null) {
for(int i = 0;i<obj.length;i++) {
ps.setObject(i+1, obj[i]);
}
}
rs = ps.executeQuery();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return rs;
}
}
util包里的info文件:
特别注意:此处是实现对不同数据库链接及增删改操作的重要部分,***链接不同的数据库要修改相对应的url,name和pwd***,修改了之后就可以实现对SQLServer或者MySql数据库等的链接及增删改操作了。
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/log?useUnicode=true&characterEncoding=utf8
name=root
pwd=root
test包里的test.java
package com.test;
import com.util.DBUtil;
import java.sql.ResultSet;
import java.sql.SQLException;
public class test {
public static void main(String[] args) {
// TODO Auto-generated method stub
//通用的增删改
String sql = "select * from user where u_name = ?";
Object obj[] = {"hwl"};
ResultSet selectS = DBUtil.executeSelect(sql, obj);
try {
while(selectS.next()) {
System.out.println(
selectS.getString(1)+"\t"+
selectS.getString(2)+"\t"+
selectS.getString(3)+"\t"+
selectS.getInt(4)+"\t"+
selectS.getString(5)+"\t"+
selectS.getString(6));
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
model包里的user.java
package com.model;
public class user {
private static String UId;
private static String UName;
private static String USex;
private static String UPhone;
private static int UMoney;
private static String UMap;
public static String getUId() {
return UId;
}
public static void setUId(String uId) {
UId = uId;
}
public static String getUName() {
return UName;
}
public static void setUName(String uName) {
UName = uName;
}
public static String getUSex() {
return USex;
}
public static void setUSex(String uSex) {
USex = uSex;
}
public static String getUPhone() {
return UPhone;
}
public static void setUPhone(String uPhone) {
UPhone = uPhone;
}
public static int getUMoney() {
return UMoney;
}
public static void setUMoney(int uMoney) {
UMoney = uMoney;
}
public static String getUMap() {
return UMap;
}
public static void setUMap(String uMap) {
UMap = uMap;
}
}
看到这里相信已经能够解决你的困惑了,快去执行数据库吧!!!