欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

JDBC: Introduction to JDBC (Part II)

程序员文章站 2022-07-13 13:42:40
...

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;
	}
}