java 数据库连接工具类

本文介绍了一个用于Java的数据库连接工具类,重点讲解了如何使用SQLUtil进行高效、便捷的SQL语句操作,帮助开发者更好地管理和执行数据库查询。
摘要由CSDN通过智能技术生成
数据库连接类: DBUtil
package com.elink.services;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

/**
 * @author lucher 
 * 本类为连接各种数据库的工具类,包括sqlserver、mysql、oracle的连接,以及关闭连接方法
 * 该类一般配合SQLUtil使用
 */
public class DBUtil {

	// sqlserver 驱动
	private static final String SQLSERVER_DRIVER = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
	
	// 数据库地址url 前面的为固定写法,后面的分别为ip地址:端口号,数据库名
//	private static final String SQLSERVER_URL = "jdbc:microsoft:sqlserver://localhost:1433;databaseName=WM";  //用微软的jar包(3个)的情况
	private static final String SQLSERVER_URL = "jdbc:sqlserver://127.0.0.1:1433;databaseName=NewMedicineDatabase";  //用普通jar包(1个)的时候
	
	private static final String SQLSERVER_USERNAME = "sa";// sqlserver 数据库用户名
	private static final String SQLSERVER_PASSWORD = "1qazxsw2";// sqlserver 数据库密码

	/**
	 * 获取SQLSERVER数据库连接
	 * @return 数据库的连接
	 */
	public static Connection getSQLSERVERConnection() {

		Connection conn = null;
		try {
			//自动创建驱动程序的实例且自动调用DriverManager来注册它
			Class.forName(SQLSERVER_DRIVER);
			conn = DriverManager.getConnection(SQLSERVER_URL, SQLSERVER_USERNAME, SQLSERVER_PASSWORD);
			System.out.println(conn);
		} catch (ClassNotFoundException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return conn;
	}

	
	
	// mysql 驱动
	private static final String MYSQL_DRIVER = "com.mysql.jdbc.Driver";
	
	// 数据库地址url 前面的为固定写法,后面的分别为ip地址:端口号,数据库名
	private static final String MYSQL_URL = "jdbc:mysql://localhost:3306/dossier";
	
	private static final String MYSQL_USERNAME = "admin";// mysql 数据库用户名
	private static final String MYSQL_PASSWORD = "admin";// mysql 数据库密码

	/**
	 * 获取MYSQL数据库连接
	 * @return 数据库的连接
	 */
	public static Connection getMYSQLConnection() {

		Connection conn = null;
		try {
			//自动创建驱动程序的实例且自动调用DriverManager来注册它
			Class.forName(MYSQL_DRIVER); 
			conn = DriverManager.getConnection(MYSQL_URL, MYSQL_USERNAME, MYSQL_PASSWORD);
		} catch (ClassNotFoundException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return conn;
	}

	
	// oracle驱动
	private static final String ORACLE_DRIVER = "oracle.jdbc.driver.OracleDriver";
	
	// 数据库地址url 前面的为固定写法,后面的分别为ip地址:端口号,数据库名
	private static final String ORACLE_URL = "jdbc:oracle:thin:@localhost:1521:dababase";
	
	private static final String ORACLE_USERNAME = "admin";// oracle 数据库用户名
	private static final String ORACLE_PASSWORD = "admin";// oracle 数据库密码
	

	/**
	 * 获取ORACLE数据库连接
	 * @return 数据库的连接
	 */
	public static Connection getORACLEConnection() {

		Connection conn = null;
		try {
			//自动创建驱动程序的实例且自动调用DriverManager来注册它
			Class.forName(ORACLE_DRIVER);
			conn = DriverManager.getConnection(ORACLE_URL, ORACLE_USERNAME, ORACLE_PASSWORD);
		} catch (ClassNotFoundException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return conn;
	}


	/**
	 * 关闭数据库连接,针对有参数的查询情况
	 * @param rs 返回的查询结果
	 * @param pstmt 
	 * @param conn
	 */
	public static void closeConn(ResultSet rs, PreparedStatement pstmt, Connection conn) {
		if (rs != null) {
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if (pstmt != null) {
			try {
				pstmt.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if (conn != null) {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	/**
	 * 关闭数据库连接重载,针对没有参数的查询情况
	 * @param rs
	 * @param ps
	 * @param conn
	 */
	public static void closeConn(ResultSet rs, Statement ps, Connection conn) {
		if (rs != null) {
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if (ps != null) {
			try {
				ps.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if (conn != null) {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	
	/**
	 * 关闭数据库连接重载,针对更新语句情况
	 * @param pstmt
	 * @param conn
	 */
	public static void closeConn(PreparedStatement pstmt, Connection conn) {
		if (pstmt != null) {
			try {
				pstmt.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if (conn != null) {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	
	/**
	 * 关闭数据库连接重载,针对同时执行两条条更新语句
	 * @param pstmt1
	 * @param pstmt2
	 * @param conn
	 */
	public static void closeConn(PreparedStatement pstmt1, PreparedStatement pstmt2, Connection conn) {
		if (pstmt1 != null) {
			try {
				pstmt1.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if (pstmt2 != null) {
			try {
				pstmt2.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if (conn != null) {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
}


SQL语句类: SQLUtil

package util;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

/**
 * @author lucher 
 * 本类为sql工具类,包括sql无参、有参查询,以及sql更新语句方法 
 * 该类一般配合DBUtil使用,只是列举了常用的方法,具体使用时稍加修改
 */
public class SQLUtil {

	// 无参数的查询语句
	private static final String sql1 = "select * from company";

	// 有参数的查询语句
	private static final String sql2 = "select password from company where name=?";

	// 更新语句
	private static final String sql3 = "update company set password=? where name=?";

	// 删除语句,同时执行
	private static final String sql4 = "delete from user where id=?";
	private static final String sql5 = "delete from userInfo where id=?";

	/**
	 * 无参数查询,返回类型据情况稍加修改,以sqlserver为例,其他类似
	 * 
	 * @return 所有记录的名字的list
	 */
	public static List<String> getAll() {

		Connection conn = null;
		Statement ps = null;
		ResultSet rs = null;
		List<String> info = new ArrayList<String>();

		try {
			conn = DBUtil.getSQLSERVERConnection();
			ps = conn.createStatement();
			rs = ps.executeQuery(sql1);

			while (rs.next()) {
				info.add(rs.getString("name")); // 根据具体情况定
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DBUtil.closeConn(rs, ps, conn);
		}
		return info;
	}

	/**
	 * 有参数查询,返回类型据情况稍加修改,以sqlserver为例,其他类似
	 * 
	 * @param name
	 * @param password
	 * @return 返回指定名字和密码的记录list
	 */
	public static List<String> getAllByNP(String name, String password) {

		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		List<String> info = new ArrayList<String>();

		try {
			conn = DBUtil.getSQLSERVERConnection();
			pstmt = conn.prepareStatement(sql1);
			pstmt.setString(1, name);
			pstmt.setString(2, password);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				info.add(rs.getString(1));
				info.add(rs.getString(2));
			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DBUtil.closeConn(rs, pstmt, conn);
		}

		return info;
	}

	/**
	 * 更新类方法,只执行一条的情况,以sqlserver为例,其他类似
	 * @param name
	 * @param password
	 * @return 执行更新的结果
	 */
	public static boolean updatePasswordByName(String name, String password) {

		int result = 0;
		boolean flag = true;

		Connection conn = null;
		PreparedStatement pstmt = null;

		try {
			conn = DBUtil.getSQLSERVERConnection();

			pstmt = conn.prepareStatement(sql3);
			pstmt.setString(1, password);
			pstmt.setString(2, name);

			result = pstmt.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DBUtil.closeConn(pstmt, conn);
		}

		if (result == 0) {
			flag = false;
		}
		return flag;
	}

	/**
	 * 更新类方法,同时执行两条的情况,以sqlserver为例,其他类似
	 * @param id
	 * @return 执行删除的结果
	 */
	public static boolean deleteById(int id) {
		
		int result1 = 0;
		int result2 = 0;
		boolean flag = true;

		Connection conn = null;
		PreparedStatement pstmt1 = null;
		PreparedStatement pstmt2 = null;

		try {
			conn = DBUtil.getSQLSERVERConnection();
			conn.setAutoCommit(false);

			pstmt1 = conn.prepareStatement(sql4);
			pstmt1.setInt(1, id);

			pstmt2 = conn.prepareStatement(sql5);
			pstmt2.setInt(1, id);

			result1 = pstmt1.executeUpdate();
			result2 = pstmt2.executeUpdate();

			conn.commit();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			try {
				conn.rollback();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
		} finally {
			DBUtil.closeConn(pstmt1, pstmt2, conn);
		}

		if (result1 == 0 || result2 == 0) {
			flag = false;
		}
		return flag;
	}
}


package com.hexiang.utils.dao; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.util.Properties; import javax.naming.Context; import javax.naming.InitialContext; import javax.naming.NamingException; import javax.sql.DataSource; import org.apache.log4j.Logger; public class DBConnection { /** * 获得与数据库的连接 * * @param path * @return Connection */ public static Connection getConn(String classDriver, String url, String user, String pwd) { try { Class.forName(classDriver); return DriverManager.getConnection(url, user, pwd); } catch (ClassNotFoundException ex) { ex.printStackTrace(); } catch (SQLException ex) { ex.printStackTrace(); } return null; } public static Connection getConn(DataSource dataSource) { try { return dataSource.getConnection(); } catch (SQLException ex) { ex.printStackTrace(); } return null; } public static Connection getConn(String jndiName) { try { Context ctx; ctx = new InitialContext(); DataSource dataSource = (DataSource) ctx.lookup("java:comp/env/" + jndiName); return dataSource.getConnection(); } catch (NamingException ex) { ex.printStackTrace(); } catch (SQLException ex) { ex.printStackTrace(); } return null; } public static Connection getConn(Properties properties) { try { String driver = properties.getProperty("jdbc.driverClassName"); String url = properties.getProperty("jdbc.url"); String user = properties.getProperty("jdbc.username"); String password = properties.getProperty("jdbc.password"); Class.forName(driver); return DriverManager.getConnection(url, user, password); } catch (ClassNotFoundException ex) { ex.printStackTrace(); } catch (SQLException ex) { ex.printStackTrace(); } return null; } /** * oracle连接 * * @param path * @return Connection */ public static Connection getOracleConn(String
评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值