JDBC: Introduction to JDBC (Part I)

1. JDBC(Java Data Base Connectivity)

        1) Composed with interfaces and classes. Mostly with interfaces as core function and minorly with classes.

        2) It is a part of JavaSE and are basically defined in java.sql and javax.sql package

 

2. Relationships of Application and JDBC API and JDBC Driver and DataBase Connection

    Comment:

        1) Different Databases have different drivers(implementations of interfaces) are offered by database vendor.

        2) Interface Oriented Programming is preferred.

 

3. Steps to connect to DB using JDBC

        1) Register Driver(Only Once is enough through whole application lifecycle)

        2) Build Connection

        3) Create SQL Statement

        4) Execute SQL Statement

        5) Process ResultSet

        6) Release Resource

package edu.xmu.dao.impl;

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

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

public class UserDaoImpl implements UserDao
{
	private String userName = "root";
	private String passWord = "root";
	static
	{
		try
		{
			// 1. Register Driver
			DriverManager.registerDriver(new com.mysql.jdbc.Driver());
		} catch (SQLException e)
		{
			e.printStackTrace();
		}
	}

	@Override
	public void addUser(User user)
	{
		Connection connection = null;
		Statement statement = null;
		String username = user.getUsername();
		String password = user.getPassword();
		String sql = "insert into user(user_name, pass_word) values ('"
				+ username + "', '" + password + "');";
		try
		{
			// 2. Build Connection
			connection = DriverManager.getConnection(
					"jdbc:mysql://localhost:3306/jdbctest", userName, passWord);
			// 3. Create SQL Statement
			statement = connection.createStatement();
			// 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 <= statement.executeUpdate(sql))
			{
				System.out.println("Insert succeed!");
			} else
			{
				System.out.println("Insert failed!");
			}

		} catch (SQLException e)
		{
			e.printStackTrace();
		} finally
		{
			// 6. Release Resources
			// If have resultSet then should also add: resultSet.close();
			try
			{
				statement.close();
			} catch (SQLException e)
			{
				e.printStackTrace();
			} finally
			{
				try
				{
					connection.close();
				} catch (SQLException e)
				{
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
		}
	}

}

    Comments:

        1) Have the risk of SQL-Injection-->Use PreparedStatement instead

        2) The cost of Building Connection is extremely high!

        3) Multiple drivers can be registered in one application or even in a single Class.

package edu.xmu.dao.impl;

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

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

public class UserDaoImpl implements UserDao
{
	private String userName = "root";
	private String passWord = "root";
	static
	{
		try
		{
			// 1. Register Driver
			DriverManager.registerDriver(new com.mysql.jdbc.Driver());
		} catch (SQLException e)
		{
			e.printStackTrace();
		}
	}

	@Override
	public void addUser(User user)
	{
		Connection connection = null;
		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 = DriverManager.getConnection(
					"jdbc:mysql://localhost:3306/jdbctest", userName, passWord);
			// 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
			// If have resultSet then should also add: resultSet.close();
			try
			{
				preparedStatement.close();
			} catch (SQLException e)
			{
				e.printStackTrace();
			} finally
			{
				try
				{
					connection.close();
				} catch (SQLException e)
				{
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
		}
	}

}

package edu.xmu.dao.impl;

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

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

public class UserDaoImpl implements UserDao
{
        //Connection pool can be used!
	private static String userName = "root";
	private static String passWord = "root";
	private static Connection connection = null;
	static
	{
		try
		{
			// 1. Register Driver
			DriverManager.registerDriver(new com.mysql.jdbc.Driver());
                        // 2. Build Connection
			connection = DriverManager.getConnection(
					"jdbc:mysql://localhost:3306/jdbctest", userName, passWord);
		} catch (SQLException e)
		{
			e.printStackTrace();
		}
	}

	@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
		{
			// 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
			// If have resultSet then should also add: resultSet.close();
			try
			{
				preparedStatement.close();
			} catch (SQLException e)
			{
				e.printStackTrace();
			}
		}
	}

	@Override
	public User getUser(int id)
	{
		// TODO Auto-generated method stub
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		User user = new User();
		String sql = "select id, user_name, pass_word from user where id = ?";
		try
		{
			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)
		{
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally
		{
			try
			{
				resultSet.close();
				preparedStatement.close();
			} catch (SQLException e)
			{
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return user;
	}

}

 

1. Driver Register (Three ways)

    1) DriverManager.registerDriver(new com.mysql.jdbc.Driver());

            ---> There is a driverList in DriverManager.

            ---> DriverManager.getConnection(URL) will make the DriverManager traverse driverList for

                   resolving this URL and create connection.

    2) System.setProperty("jdbc.drivers", "com.mysql.jdbc.Driver:com.oracle.jdbc.Driver");

            ---> Use ":" as seperator for multiple Driver Register

    3) Class.forName("com.mysql.jdbc.Driver");

            ---> Just load com.mysql.jdbc.Driver into JVM. And havn't created any instance of this Driver

            ---> How/When did they register this Driver into DriverManager(Put this driver into driverList)?

            ---> When  Class.forName("com.mysql.jdbc.Driver") is executed, com.mysql.jdbc.Driver extends Driver, so the static {java.sql.DriverManager.registerDriver(new Driver());} will execute. The driver itself will be added into the driverList.

    Comment:

            1) The third method is prefered.

                 Because when using DriverManager.registerDriver(new com.mysql.jdbc.Driver(...));

                 The new com.mysql.jdbc.Driver() will execute static{java.sql.DriverManager.registerDriver(...)}

                 And then we use DriverManager.registerDriver(...); will cause register the same dirver again.

            2) The third method is prefered.

                 Because when using DriverManager.registerDriver(new com.mysql.jdbc.Driver(...));

                 This statement will make this class depend on the com.mysql.jdbc.Driver class and violate AOP.

                 The second and third method will not violate AOP because the app depends on String/Config.

                 The program will run successfully when we don't have the mysql jdbc driver when using 2nd or 3rd way. And will not proceed successfully when using 1st way.

try
		{
			// 1. Register Driver
			Class.forName("com.mysql.jdbc.Driver");
			connection = DriverManager.getConnection(
					"jdbc:mysql://localhost:3306/jdbctest", userName, passWord);
		} catch (SQLException e)
		{
			e.printStackTrace();
		} catch (ClassNotFoundException e)
		{
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

 

2. Build Connection

private static Connection connection = null;
connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbctest", userName, passWord);

    1) URL Format:

          1) jdbc:subProtocol:subName://hostName:portnumber/databasename?propertyName=propertyValue&propertyName=propertyValue

          2) user, password can set as user=username&password=password as key-value in URL

          3) Other prarams: useUnicode=true&characterEncoding=GBK

    2) Don't forget close connection after used.-->Best Practice: Build connection as late as possible and close connection as early as possible.