jdbcutil操作数据库

最新推荐文章于 2023-04-12 16:26:05 发布

原创最新推荐文章于 2023-04-12 16:26:05 发布 · 119 阅读

0 ·

CC 4.0 BY-SA版权

java 专栏收录该内容

68 篇文章

订阅专栏

本文介绍了JDBC工具类的实现与数据库操作的基本流程，包括获取连接、执行SQL语句、预编译语句及资源释放。通过具体代码示例展示了如何使用JDBC进行数据库操作，并提供了CRUD操作的实现。同时，文章还讨论了JDBC事务管理在模拟银行取款过程中的应用，以及如何处理事务回滚和提交。

JdbcUtil

package jdbcConnection;

import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Properties;

/**
 * jdbc工具类
 * 
 * @author happyqing
 * 
 */
public class JdbcUtil {

	private static Properties env = new Properties();
	static {
		try {
			//JdbcUtil.class.getResourceAsStream("/env.properties"); // /com/cici/conf/env.properties
			InputStream is = JdbcUtil.class.getClassLoader().getResourceAsStream("env.properties");
			env.load(is);
			is.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	private static final ThreadLocal<Connection> tl = new ThreadLocal<Connection>();

	public static Connection getConnection() {
		Connection conn = tl.get();
		try {
			if (conn == null) {

				Class.forName(env.getProperty("jdbc.driverClassName"));
				conn = DriverManager.getConnection(env.getProperty("jdbc.url"),
						env.getProperty("jdbc.username"),
						env.getProperty("jdbc.password"));
				tl.set(conn);

			}
			// 设置不自动提交
			conn.setAutoCommit(false);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return conn;
	}

	/**
	 * 创建PreparedStatement
	 * 
	 * @param conn
	 * @param sql
	 * @return
	 */
	public static PreparedStatement getPreparedStatement(Connection conn,
			String sql) {
		PreparedStatement psmt = null;
		if (conn != null) {
			try {
				psmt = conn.prepareStatement(sql);
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return psmt;
	}

	/**
	 * 释放资源
	 */
	public static void close(ResultSet rs, Statement stm, Connection conn) {
		if (rs != null)
			try {
				rs.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		if (stm != null)
			try {
				stm.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		if (conn != null)
			try {
				conn.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
	}

	// 测试
	public static void main(String[] args) {
		Connection conn = JdbcUtil.getConnection();
		System.out.println(conn);
	}
}

env.properties

jdbc.driverClassName= oracle.jdbc.driver.OracleDriver
jdbc.url= jdbc:oracle:thin:@192.168.1.186:1521:orcl
jdbc.username=xxx
jdbc.password=xxx

crud

package jdbcConnection;   
  
import java.sql.Connection;   
import java.sql.PreparedStatement;   
import java.sql.ResultSet;   
import java.sql.SQLException;   
import java.util.Date;   
  
import utils.JdbcUtil;   
  
public class CrudTest {   
  
    private static void add() throws SQLException {   
        // TODO Auto-generated method stub   
        Connection conn = null;   
        PreparedStatement ps = null;   
        ResultSet rs = null;   
        try {   
            conn = JdbcUtil.getConnection();   
            String sql = "insert into [user](name,password,email,age,birthday,money) values(?,?,?,?,?,?)";   
            ps = conn.prepareStatement(sql);   
            ps.setString(1, "psName");   
            ps.setString(2, "psPassword");   
            ps.setString(3, "jkjs@126.com");   
            ps.setInt(4, 23);   
            ps.setDate(5, new java.sql.Date(new java.util.Date().getDate()));   
            ps.setFloat(6, 2344);   
            ps.executeUpdate();   
        } finally {   
            JdbcUtil.close(rs, ps, conn);   
        }   
  
    }   
  
    private static void get() throws SQLException {   
        // TODO Auto-generated method stub   
        Connection conn=null;   
        PreparedStatement ps =null;   
        ResultSet rs = null;   
        try {   
            conn= JdbcUtil.getConnection();   
             String sql = "select id,name,password,email,birthday,money from [user]";    
             ps = conn.prepareStatement(sql);   
             rs= ps.executeQuery();   
             while(rs.next()){      
                    int id= rs.getInt("id");      
                    String name =rs.getString("name");      
                    String pass=rs.getString("password");    
                    String email = rs.getString("email");   
                    Date birthday =rs.getDate("birthday");    
                    float money= rs.getFloat("money");   
                    System.out.println("id是："+id+"姓名是： "+name+" 密码是："+pass+"邮箱是："  
                            +email+"生日是： "+birthday+"工资是"+money);      
                }      
               
        } finally{   
            JdbcUtil.close(rs, ps, conn);   
        }   
           
    }   
  
    private static void update() throws SQLException {   
        // TODO Auto-generated method stub   
        Connection conn=null;   
        PreparedStatement ps =null;   
        ResultSet rs = null;   
        try {   
            conn= JdbcUtil.getConnection();   
            String sql = "update [user] set name='lucy',password='123',money=5000 where id=1";   
            ps= conn.prepareStatement(sql);   
            ps.executeUpdate();   
               
        } finally{   
            JdbcUtil.close(rs, ps, conn);   
        }   
           
    }   
  
    private static void delete() throws SQLException {   
        // TODO Auto-generated method stub   
        Connection conn=null;   
        PreparedStatement ps =null;   
        ResultSet rs = null;   
        try {   
            conn= JdbcUtil.getConnection();   
            String sql = "delete from [user] where id=1";   
            ps= conn.prepareStatement(sql);   
            ps.executeUpdate();   
               
        } finally{   
            JdbcUtil.close(rs, ps, conn);   
        }   
           
    }   
}

Jdbc事务管理

模拟银行取帐

TransferTest.java

package test;   
  
import java.sql.Connection;   
import java.sql.PreparedStatement;   
import java.sql.ResultSet;   
import java.sql.SQLException;   
  
import util.JdbcUtil;   
  
public class TransactionTest {   
    public static void main(String[] args)throws SQLException {   
        transferTest();   
           
    }   
    public static void transferTest()throws SQLException{   
        int id1 =3;   
        float m1= 100.0f;   
        int id2 = 4;   
        transfer2(id1,m1,id2);   
    }   
       
    public static boolean transfer2(int id1,float m1,int id2)throws SQLException{   
        boolean flag = false;   
        Connection conn =null;   
        PreparedStatement ps = null;   
        ResultSet rs = null;   
        try {   
            conn = JdbcUtil.getConnection();   
            conn.setAutoCommit(false);   
            String sql = "update [user] set money = money=? where id = ?";   
            ps= conn.prepareStatement(sql);   
            ps.setFloat(1, m1);   
            ps.setInt(2, id1);   
            ps.executeUpdate();   
               
               
               
            String sql1 = "update [user] set money = money+? where id = ?";   
            ps= conn.prepareStatement(sql);   
            ps.setFloat(1, m1);   
            ps.setInt(2, id2);   
            ps.executeUpdate();   
               
            conn.commit();   
        } catch (SQLException e) {   
            // TODO 自动生成 catch 块   
            conn.rollback();   
            throw e;   
        }finally{   
            JdbcUtil.close(rs, ps, conn);   
        }   
        return flag;   
           
           
    }   
       
  
}

java读取properties文件

http://happyqing.iteye.com/admin/blogs/1966014

JDBC连接各种数据库方法
http://happyqing.iteye.com/admin/blogs/1966028
使用JDBC进行批处理
http://happyqing.iteye.com/admin/blogs/1965951