JDBC: Introduction to JDBC (Part II)

1. Good Practice: 

        1>Build Connection as late as possible.

        2>Close Connection as earyl as possible.

        3>Connection Pool can be used to enhance performance.

 

2. Good Example:

 

        1> JdbcUtil.java

package utils;

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


public final class JdbcUtil
{
	// Trying to make all secret filed as private.
	private static String url = "jdbc:mysql://localhost:3306/jdbctest";
	private static String username = "root";
	private static String password = "root";
	// Enable to make sure driver is only registered once.
	static
	{
		try
		{
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e)
		{
			e.printStackTrace();
		}
	}
	
	// Disable user to instantiate JdbcUtil
	private JdbcUtil()
	{
	}
	public static Connection getConnection() throws SQLException
	{
		return DriverManager.getConnection(url, username, password);
	}
	// Have to judge if a certain field is null
	public static void releaseResource(ResultSet resultSet,
			Statement statement, Connection connection)
	{

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

 

        2>UserDaoImpl.java

package edu.xmu.dao.impl;

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

import utils.JdbcUtil;

import edu.xmu.dao.UserDao;
import edu.xmu.domain.User;

public class UserDaoImpl implements UserDao
{
	private static Connection connection = null;

	@Override
	public void addUser(User user)
	{
		PreparedStatement preparedStatement = null;
		String username = user.getUsername();
		String password = user.getPassword();
		String sql = "insert into user(user_name, pass_word) values (?, ?)";
		try
		{
			// 2. Build Connection
			connection = JdbcUtil.getConnection();
			// 3. Create SQL Statement
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setString(1, username);
			preparedStatement.setString(2, password);

			// 4. Execute SQL Statement
			// 5. If statement is C/U/D, returns integer value indicating rows
			// affected
			// If statement is R, returns result set.
			if (0 <= preparedStatement.executeUpdate())
			{
				System.out.println("Insert succeed!");
			} else
			{
				System.out.println("Insert failed!");
			}

		} catch (SQLException e)
		{
			e.printStackTrace();
		} finally
		{
			// 6. Release Resources
			JdbcUtil.releaseResource(null, preparedStatement, connection);
		}
	}

	@Override
	public User getUser(int id)
	{
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		User user = new User();
		String sql = "select id, user_name, pass_word from user where id = ?";
		try
		{
			connection = JdbcUtil.getConnection();
			preparedStatement = connection.prepareStatement(sql);

			preparedStatement.setInt(1, id);

			resultSet = preparedStatement.executeQuery();

			while (resultSet.next())
			{
				user.setId(id);
				user.setUsername(resultSet.getString("user_name"));
				user.setPassword(resultSet.getString("pass_word"));
			}
		} catch (SQLException e)
		{
			e.printStackTrace();
		} finally
		{
			JdbcUtil.releaseResource(resultSet, preparedStatement, connection);
		}
		return user;
	}
}