java封装oracle jdbc_jdbc链接Oracle数据库的封装

在src下创建properties文件

driver=oracle.jdbc.driver.OracleDriver

url=jdbc:oracle:thin:@//127.0.0.1:1521/XE

username=scott

password=123456

package 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.util.Properties;

public class JDBCUitl {

private static String driver;

private static String url;

private static String username;

private static String password;

static {

// 创建properties对象获取文件

Properties ps = new Properties();

// 获取流对象

InputStream is = JDBCUitl.class.getResourceAsStream("/driver.properties");

try {

// 加载文件

ps.load(is);

driver = ps.getProperty("driver");

url = ps.getProperty("url");

username = ps.getProperty("username");

password = ps.getProperty("password");

// 加载驱动

Class.forName(driver);

} catch (IOException e) {

e.printStackTrace();

} catch (ClassNotFoundException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

}

/**

* 获取链接对象

*

* @return conn

*/

public static Connection getConnection() {

Connection conn = null;

try {

conn=DriverManager.getConnection(url, username, password);

} catch (SQLException e) {

e.printStackTrace();

}

return conn;

}

/**

* 创建链接对象

*

* @param conn

* @param sql

* @return ps

*/

public static PreparedStatement getPreparedStatement(Connection conn, String sql) {

PreparedStatement ps = null;

try {

ps = conn.prepareStatement(sql);

} catch (SQLException e) {

e.printStackTrace();

}

return ps;

}

/**

* 增刪改通用语句

*

* @param sql

* @param objs

* @return i

*/

public static int executeDML(String sql, Object... objs) {

Connection conn = getConnection();

PreparedStatement ps = getPreparedStatement(conn, sql);

try {

conn.setAutoCommit(false);

for (int i = 0; i < objs.length; i++) {

ps.setObject(i + 1, objs[i]);

}

int i = ps.executeUpdate();

conn.commit();

return i;

} catch (SQLException e) {

try {

conn.rollback();

} catch (SQLException e1) {

e1.printStackTrace();

}

e.printStackTrace();

}

return -1;

}

public void allClose(ResultSet rs, PreparedStatement ps, Connection conn) {

try {

if (rs != null) {

rs.close();

}

} catch (SQLException e) {

e.printStackTrace();

}

try {

if (ps != null) {

ps.close();

}

} catch (SQLException e) {

e.printStackTrace();

}

try {

if (conn != null) {

conn.close();

}

} catch (SQLException e) {

e.printStackTrace();

}

}

}

package daoImp;

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.util.ArrayList;

import dao.EmpDao;

import pojo.Emp;

import util.JDBCUitl;

public class EmpDaoImp implements EmpDao {

/**

* 实现查询所有用户信息

*/

@Override

public ArrayList selAllEmpInfo() {

String sql = "select * from empe";

Connection conn = JDBCUitl.getConnection();

PreparedStatement ps = JDBCUitl.getPreparedStatement(conn, sql);

ArrayList empList = new ArrayList();

try {

ResultSet rs = ps.executeQuery();

while (rs.next()) {

Emp e = new Emp();

e.setEmpId(rs.getInt("empid"));

e.setName(rs.getString("ename"));

e.setEmoney(rs.getInt("emoney"));

e.setEqq(rs.getString("eqq"));

empList.add(e);

}

} catch (SQLException e) {

e.printStackTrace();

}

return empList;

}

/**

* 根据id查询用户信息

*/

@Override

public Emp selEmpByEmpId(int empId) {

String sql = "select * from empe where empid=?";

Connection conn = JDBCUitl.getConnection();

PreparedStatement ps = JDBCUitl.getPreparedStatement(conn, sql);

Emp emp = null;

try {

ps.setInt(1, empId);

ResultSet rs = ps.executeQuery();

if (rs.next()) {

emp = new Emp();

emp.setEmpId(rs.getInt("empid"));

emp.setName(rs.getString("ename"));

emp.setEmoney(rs.getInt("emoney"));

emp.setEqq(rs.getString("eqq"));

}

} catch (SQLException e) {

e.printStackTrace();

}

return emp;

}

/**

* 添加用户信息

*/

@Override

public int insEmpInfo(int empId, String ename, int emoney, String eqq) {

String sql = "insert into empe values(?,?,?,?)";

int i = JDBCUitl.executeDML(sql, empId, ename, emoney, eqq);

return i;

}

/**

* 根据用户id更改姓名

*/

@Override

public int upEmpByEmpId(String newName, int empId) {

String sql = "update empe set ename=? where empid=?";

int i = JDBCUitl.executeDML(sql, newName, empId);

return i;

}

/**

* 根据id删除用户信息

*/

@Override

public int delEmpByEmpId(int empId) {

String sql = "delete from empe where empid=?";

int i = JDBCUitl.executeDML(sql, empId);

return i;

}

}