hp培训之JDBC优化

 1.      写一个java程序对数据库进行增，删，改，查操作

2.      优化程序

a.     使用DAO类优化程序（接口）

b.     使用常量优化程序（连接字符串的类）

c.     使用util类优化程序（关闭类）

 

package com.training.dao;

import java.sql.SQLException;

public interface StudentDao {
	 void insert(String sql) throws SQLException;
	 void update(String sql);
	 void delete(String sql);
	 void select(String sql);
}

 

 

package com.training.dapImpl;

import java.sql.Connection;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.SQLException;
import com.training.util.JdbcUtil;
import com.training.util.ResultSetUtil;
import com.training.dao.StudentDao;

public class StudentDaoImpl implements StudentDao{
	public void insert(String sql) throws SQLException
	{
		Connection con = null;
		Statement st = null;

		try
		{
			con = JdbcUtil.getConnection();
			st = con.createStatement();
			//String sql = "insert into student(id, name, course) values(100, 'allan', 'some course')";
			System.out.println(sql);
			st.executeUpdate(sql);			
		}catch(Exception e)
		{
			e.printStackTrace();
			throw new SQLException(e.getMessage());
		}finally
		{			
			JdbcUtil.close(st, con);
		}
	}
	
	public void update(String sql)
	{
		Connection con = null;
		Statement st = null;

		try
		{
			con = JdbcUtil.getConnection();
			st = con.createStatement();
			//String sql = "update student set name='new name' where id=100";
			st.executeUpdate(sql);			
		}catch(Exception e)
		{
			e.printStackTrace();
			throw new RuntimeException(e.getMessage());
		}finally
		{			
			JdbcUtil.close(st, con);
		}
	}
	
	public void delete(String sql)
	{
		Connection con = null;
		Statement st = null;

		try
		{
			con = JdbcUtil.getConnection();
			st = con.createStatement();
			//String sql = "delete from student where id=100";
			System.out.println(sql);
			st.executeUpdate(sql);			
		}catch(Exception e)
		{
			e.printStackTrace();
			throw new RuntimeException(e);
		}finally
		{			
			JdbcUtil.close(st, con);
		}
	}
	
	public void select(String sql)
	{
		Connection con = null;
		Statement st = null;
		ResultSet rs = null;
		
		
		try
		{
			con = JdbcUtil.getConnection();
			st = con.createStatement();
			//String sql = "select * from student";
			System.out.println(sql);
			rs = st.executeQuery(sql);
			ResultSetUtil.printRS(rs);
			
			//注意ResultSet和ResultSetMetaData的区别
//			while(rs.next())
//			{
//				System.out.print("sno=" + rs.getInt("sno"));
//				System.out.print(",name=" + rs.getString("name"));
//				System.out.print(",sex=" + rs.getString("sex"));
//				System.out.print(",age="+rs.getInt("age"));
//				System.out.print(",dept="+rs.getString("dept"));
//				System.out.println("");
//			}
		}catch(Exception e)
		{
			e.printStackTrace();
			throw new RuntimeException(e);
		}finally
		{
			JdbcUtil.close(rs, st, con);
		}
	}
}

 

 

package com.training.util;

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

public class JdbcUtil {
	public static void close(Statement st, Connection con)
	{
		try
		{
			st.close();
		}catch(Exception e)
		{
		}
		
		try
		{
			con.close();
		}catch(Exception e)
		{
		}
	}
	
	public static void close(ResultSet rs, Statement st, Connection con)
	{
		try
		{
			rs.close();
		}catch(Exception e)
		{
		}
		close(st, con);
	}
	
	public static Connection getConnection() throws Exception
	{
		Class.forName(SqlConstant.DB_NAME);
		return DriverManager.getConnection(SqlConstant.DB_URL, SqlConstant.DB_USER_NAME, SqlConstant.DB_PASSWORD);
	}
}

 

package com.training.util;

import java.sql.*;

public class ResultSetUtil {
	public static void printRS(ResultSet rs) throws SQLException
	{
		ResultSetMetaData rsmd = rs.getMetaData();
		int colCount = rsmd.getColumnCount();
		
		while(rs.next())
		{
			for(int i = 1; i <= colCount; i++)
			{
				if(i > 1)
				{
					System.out.print(",");
				}
				String name = rsmd.getColumnName(i);
				String value = rs.getString(i);
				System.out.print(name + "=" + value);
			}
			System.out.println();
		}
	}
}

 

 

package com.training.util;

public class SqlConstant {
	public static final String DB_NAME = "com.mysql.jdbc.Driver";
	public static final String DB_URL = "jdbc:mysql://localhost:3306/test";
	public static final String DB_USER_NAME = "root";
	public static final String DB_PASSWORD = "admin";
}

 

 

package com.training.jdbc;

import com.training.dao.StudentDao;
import com.training.dapImpl.StudentDaoImpl;

public class SecondMySqlJdbc {
	public static void main(String[] args) {
		StudentDao sd = new StudentDaoImpl();
		try {
			//String sql="select * from student";
			//sd.insert();
			//sd.update();
			//sd.delete();
			sd.select("select * from student");
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
}