知识点:
- 1.JDBC操纵数据库的梳理和总结:
https://blog.csdn.net/qq_40301026/article/details/89603410
- 2.Properties类的理解和使用Properties读取配置文件
https://blog.csdn.net/qq_40301026/article/details/89598256
- 3.getResourceAsStream的理解和用法
https://blog.csdn.net/qq_40301026/article/details/89598142
代码注释即为思路:(没有封装sql查询语句)
package cn.liu.jbdc.util;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
/**
* 问题:
开发过程中尽量要求,在不重启程序的情况下完成对代码的参数的修改.
解决:
将jdbc参数存储到properties属性配置文件中,封装工具类进行获取.
知识点:
properties文件是专门用来存储属性配置的文件,格式要求必须是键值对,以=号隔开.一行一组键值对,并且不能使用分号结尾. 可以使用Properties对象来进行读取该文件的内容.
* @author Dick
*
*/
public class JdbcUtil {
private static String driverPackage;
private static String url;
private static String user;
private static String password;
//静态块来实现对properties配置文件的读取,对静态变量进行填充
static {
//1.获得Properties对象
Properties pro = new Properties();
//获取创建的Properties配置文件的流对象
InputStream is = JdbcUtil.class.getResourceAsStream("/db.properties");
try {
//装载此流,建立稳定的io流
pro.load(is);
//从此流中获取配置文件的数据
driverPackage = pro.getProperty("driverPackage");
url = pro.getProperty("url");
user = pro.getProperty("user");
password = pro.getProperty("password");
} catch (IOException e) {
e.printStackTrace();
}
}
//创建获取Connection对象的静态方法
public static Connection getConnection() {
Connection conn = null;
try {
//加载驱动包
Class.forName(driverPackage);
//获取数据库连接对象(Connection对象)
conn = DriverManager.getConnection(url, user, password);
} catch (ClassNotFoundException e) {
e.printStackTrace();
}catch (SQLException e) {
e.printStackTrace();
}
return conn;
}
//创建获取Statement对象的静态方法
public static Statement getStatement() {
Connection conn = getConnection();
//获取sql命令对象
Statement state = null;
try {
state = conn.createStatement();
} catch (SQLException e) {
e.printStackTrace();
}
return state;
}
//创建获取PreparedStatement对象的静态方法
public static PreparedStatement getperparedStatement(String sql ,Connection conn) {
//创建sql命令(直接从参数传进来)
//获取sql命令对象
PreparedStatement state = null;
try {
state = conn.prepareStatement(sql);//对sql语句进行了预编译
} catch (SQLException e) {
e.printStackTrace();
}
return state;
}
//创建关闭资源静态方法
public static void close(ResultSet re , Connection conn , Statement state) {
/**
* 说明:1.上面的参数如果那个缺少,可以直接赋为null
* 2.PreparedStatement是Statement的子类,利用多态,PreparedStatement也是Statement的一种
*/
try {
if(re!=null) {
re.close();
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
try {
if(conn!=null) {
conn.close();
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
try {
if(state!=null) {
state.close();
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
//创建增删改的封装方法--executeDML
public static int execute(String sql , Object... objs) {
//说明:1.sql语句是从外面传入的,
// 2.Object是所有参数类型的祖宗,可变参数底层是一个长度确定的数组。
//获得Connection对象
Connection conn = getConnection();
//获得perparedStatement对象
PreparedStatement state = getperparedStatement(sql,conn);
try {
//占位符赋值
for(int i = 0;i<objs.length;i++) {
state.setObject(i+1, objs[i]);
}
//执行sql命令
int i = state.executeUpdate();
return i ;
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
close(null, conn, state);
}
return -1;
}
}
配置文件,以后直接在这里面传参。
或是:
package cn.liu.util;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import cn.liu.pojo.User;
public class JdbcUtil {
private static String driverPackage;
private static String url;
private static String user;
private static String password;
static{
driverPackage="oracle.jdbc.driver.OracleDriver";
url="jdbc:oracle:thin://@DESKTOP-F4QSNQL:1521/XE";
user="scott";
password="qwer";
}
//获取Connection连接对象
public static Connection getconn(){
Connection conn = null;
try {
//加载驱动包
Class.forName(driverPackage);
//获取数据库连接对象
conn = DriverManager.getConnection(url, user, password);
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return conn;
}
//获取命令操作对象
public static PreparedStatement getstate(Connection conn,String sql){
PreparedStatement state = null;
try {
//对sql语句进行预编译
state = conn.prepareStatement(sql);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return state;
}
//查找
public static Object select(String sql,Object... obj){
//检查sql语句是否为select * from 表名
String sql1="select * from t_user";
//获取数据库连接对象
Connection conn = JdbcUtil.getconn();
//获取sql命令对象
PreparedStatement state = JdbcUtil.getstate(conn, sql);
if(sql.equals(sql1)){
try {
//执行sql
ResultSet re = state.executeQuery();
//创建一个List存储User
List<User> list = new ArrayList<User>();
while(re.next()){
User user= new User();
user.setTid(re.getInt("tid"));
user.setUname(re.getString("uname"));
user.setPwd(re.getString("pwd"));
user.setSex(re.getString("sex"));
user.setAge(re.getInt("age"));
list.add(user);
}
//释放资源
JdbcUtil.close(conn, state, re);
return list;
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}else{//其他查询
User user = null;
//给占位符
try {
for(int i= 0;i<obj.length;i++){
state.setObject(i+1, obj[i]);
}
//执行sql
ResultSet re = state.executeQuery();
while(re.next()){
user = new User();
user.setTid(re.getInt("tid"));
user.setUname(re.getString("uname"));
user.setPwd(re.getString("pwd"));
user.setSex(re.getString("sex"));
user.setAge(re.getInt("age"));
}
//释放资源
JdbcUtil.close(conn, state, re);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return user;
}
return null;
}
//增删改
public static int executeUpdate(String sql,Object...obj){
int k = 0;
try {
//获取数据库连接对象
Connection conn = JdbcUtil.getconn();
conn.setAutoCommit(false);//设置为手动提交
//获取sql命令对象
PreparedStatement state = JdbcUtil.getstate(conn, sql);
//给占位符
for(int i= 0;i<obj.length;i++){
state.setObject(i+1, obj[i]);
}
//执行sql
k = state.executeUpdate();
if(k>0){
conn.commit();
}else{
conn.rollback();
}
//释放资源
JdbcUtil.close(conn, state, null);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return k;
}
//关闭相关资源
public static void close(Connection conn ,Statement state,ResultSet re){
try {
if(state!=null){
state.close();
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
try {
if(conn!=null){
conn.close();
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
try {
if(re!=null){
re.close();
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}