package cn.com.JDBC;

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

public class CRUD {

	public static void main(String[] args) throws SQLException {
		// TODO Auto-generated method stub
		//create();
		//update();
		delete();
		read();
	}
	static void delete() throws SQLException
	{
		Connection conn=null;
		Statement st=null;
		ResultSet resultset=null;
		
		try {
			//2.建立连接
			conn=JdbcUtils.getConnection();
			//单例设计模式
			conn=JdbcUtilsSingle.getInstance().getConnection();
			//3.创建语句
			st=conn.createStatement();
			//4.执行语句
			String sql="delete from user where id>5";
			int i=st.executeUpdate(sql);
			
			System.out.println("i="+i);
		} finally
		{
			JdbcUtils.free(resultset, st, conn);
		}
	}
	static void update() throws SQLException
	{
		Connection conn=null;
		Statement st=null;
		ResultSet resultset=null;
		
		try {
			//2.建立连接
			conn=JdbcUtils.getConnection();
			//单例设计模式
			conn=JdbcUtilsSingle.getInstance().getConnection();
			//3.创建语句
			st=conn.createStatement();
			//4.执行语句
			String sql="update user set money=money+20";
			int i=st.executeUpdate(sql);
			
			System.out.println("i="+i);
		} finally
		{
			JdbcUtils.free(resultset, st, conn);
		}
	}
	static void create() throws SQLException
	{
		Connection conn=null;
		Statement st=null;
		ResultSet resultset=null;
		
		try {
			//2.建立连接
			conn=JdbcUtils.getConnection();
			//单例设计模式
			conn=JdbcUtilsSingle.getInstance().getConnection();
			//3.创建语句
			st=conn.createStatement();
			//4.执行语句
			String sql="insert into user(name,birthday,money) values('wy','2011-09-23','2894656')";
			int i=st.executeUpdate(sql);
			
			System.out.println("i="+i);
		} finally
		{
			JdbcUtils.free(resultset, st, conn);
		}
	}
	static void read() throws SQLException
	{
		Connection conn=null;
		Statement st=null;
		ResultSet resultset=null;
		
		try {
			//2.建立连接
			conn=JdbcUtils.getConnection();
			//单例设计模式
			conn=JdbcUtilsSingle.getInstance().getConnection();
			//3.创建语句
			st=conn.createStatement();
			//4.执行语句
			resultset=st.executeQuery("select id,name,birthday,money from user");
			//5.处理结果
			while(resultset.next())
			{
				System.out.println(resultset.getObject("id"));
				System.out.println(resultset.getObject("name"));
				System.out.println(resultset.getObject("birthday"));
				System.out.println(resultset.getObject("money"));
			}
			
		} finally
		{
			JdbcUtils.free(resultset, st, conn);
		}
	}
}
package cn.com.JDBC;

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

public class JdbcUtils 
{
	private static String url="jdbc:mysql://localhost:3306/jdbc";
	private static String user="root";
	private static String password="123";
	private JdbcUtils()
	{
	}
	static
	{
		try
		{
			Class.forName("com.mysql.jdbc.Driver");
		}
		catch(ClassNotFoundException e)
		{
			throw new ExceptionInInitializerError(e);
		}
	}
	public static Connection getConnection() throws SQLException
	{
		return DriverManager.getConnection(url, user, password);
	}
	public static void free(ResultSet resultset,Statement st,Connection conn)
	{
		//6.释放资源
		try{
		if(resultset!=null)
			resultset.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();
					}
			}
		}
	}
}