jdbcutil连接数据库

JdbcUtils

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



public class JdbcUtil {
	private static String url = "jdbc:sqlserver://localhost:1433;DataBaseName=db_test";
	private static String username = "sa";
	private static String password = "sa";
	private static String driverName = "com.microsoft.sqlserver.jdbc.SQLServerDriver";

	public JdbcUtil() {
		super();
		// TODO Auto-generated constructor stub
	}

	static {
		try {
			Class.forName(driverName);
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	public static Connection getConnection() throws SQLException {
		return DriverManager.getConnection(url, username, password);
	}

	public static void close(ResultSet rs, Statement st, Connection conn) {
		try {
			if (rs != null) {
				rs.close();

			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			try {
				if(st!=null){
					st.close();
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}finally{
				if(conn!=null){
					try {
						conn.close();
					} catch (SQLException e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					}
				}
			}
		}

	}

}

 

Crude。java

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, "[e-mail]");
			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;
		
		
	}
	

}

 必须有回滚处理

否则可能会造成数据混乱！