JDBC( Java Data Base Connectivity ) is one of the technique of JavaEE.
How to use JDBC to query the data in the table and print the data in the console via java?
1. Register the sql driver
2. Get the connection with database
3. Create the sql statement object
4. Execute the sql statement
5. If you execute the query statement, you should handle the result
6. Close the resource
for exmaple:
public void test() throws Exception{
// register the driver
//DriverManager.register(new com.mysql.jdbc.Driver()); ----------> not recommended
Class.forName("com.mysql.jdbc.Driver()");
// get the connection of database
Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/day15","root","root");
// create statement object
Statement statement = connection.createStatement();
// execute the sql statement
ResultSet results = statement.executeQuery('SELECT * FROM users');
// get the information in the table
while(results.next()){
String id = results.getObject("id");
String name = results.getObject("name");
String password = results.getObject("password");
String email = results.getObject("email");
String birthday = results.getObject("birthday");
}
// close the resource
results.close();
statement.close();
connection.close();
}
DriverManager:
function:
1. register the driver:
Class.forName("com.mysql.jdbc.Driver");
2. get the connection:
Connection connection = DriverManager.getConnecion("jdbc:mysql://localhost:3306/mydb1","root","root");
three ways to get the connection:
method1:
DriverManager.getConnection("jdbc:mysql://localhost:3306/mydb1","root","root");
method2:
Properties prop = new Properties();
prop.put("user","root");
prop.put("password","root");
DriverManager.getConnection("jdbc:mysql://localhost:3306/mydb1",prop);
method3:
DriverManager.getConnection("jdbc:mysql://localhost:3306?user=root&password=root");
Connection: represents the database's connection, all the operations between client and datase are completed via this object.
get the Statemtn object via Connection:
Statement statement = connection.createStatement();
Statement: the sql statement object:
ResultSet set = statement.executeQuery(String sql); ----> the sql must be query statement
int num = statement.executeUpdate(String sql); -----> the sql must be DML statement( INSERT UPDATE DELETE ) or the
no-return-value DDL statement; the return value is the affected row
boolean flag = statement.execute(String sql); -----> execute the sql statement, if the return value is ResultSet, the flag is true, else is false
ResultSet: the database result's set, returned by the sql query statement.
boolean flag = ResultSet.next(); -----> move the cursor to the next position, the return value: has the result or not
boolean flag = ResultSet.previous(); -----> move the cursor to the previous position, the return value: has the result or not
boolean flag = ResultSet.absolute(int row); -----> move the cursor to the specific position
void beforeFirst(); -----> move the cursor to before the first result
void afterLast(); -----> move the cursor to the next the last result
Object getObject(int fieldIndex); -----> get the field by index
Object getObject(String fieldName); -----> get the field by the field's name
Close the resource:
try{...}
catch(){...}
finally{
if(resultSet!=null){
try{
resultSet.close();
}catch(SQLException e){
e.printStackTrace();
}
resultSet = null;
}
if(statement != null){
try{
statement.close();
}catch(SQLException e){
e.printStackTrace();
}
statement = null;
}
if(connection != null){
try{
connection.close()
}catch(SQLException e){
e.printStackTrace();
}
connection = null;
}
}
JdbcUtil:
package com.pp; import java.io.IOException; import java.io.InputStream; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.util.Properties; public class JdbcUtil { private static String classDriver; private static String url; private static String user; private static String password; static { try { ClassLoader loader = JdbcUtil.class.getClassLoader(); InputStream in = loader .getResourceAsStream("dataBaseConfig.properties"); Properties prop = new Properties(); prop.load(in); classDriver = prop.getProperty("classDriver"); url = prop.getProperty("url"); user = prop.getProperty("user"); password = prop.getProperty("password"); } catch (IOException e) { throw new ExceptionInInitializerError( "Failed to get the information of data base config file!"); } try { Class.forName(classDriver); } catch (ClassNotFoundException e) { throw new ExceptionInInitializerError("Failed to load the driver!"); } } public static Connection getConnection() { try { Connection connection = DriverManager.getConnection(url, user, password); return connection; } catch (Exception e) { throw new RuntimeException("Fail to get the connection!"); } } public static void release(Connection connection, Statement statement, ResultSet set) { if (set != null) { try { set.close(); } catch (SQLException e) { e.printStackTrace(); } set = null; } if (statement != null) { try { statement.close(); } catch (SQLException e) { e.printStackTrace(); } statement = null; } if (connection != null) { try { connection.close(); } catch (SQLException e) { e.printStackTrace(); } connection = null; } } }
dataBaseConfig.properties:
classDriver=com.mysql.jdbc.Driver url=jdbc:mysql://localhost:3306/mydb1 user=root password=root
If you copy a web project, don't forget to change the Web Context Root in the project's properties:
SQL Injection:
"SELECT * FROM user WHERE username='abc' and password='' or 1=1 or username='' ;"
When you input this SQL statement, you will find this statement has the equal effect of "SELECT * FROM users; ". So it's dangerous.
PreparedStatement: is the prepared statemen object, it has hign effeciency.
Advantages:
1. avoid SQL injection
2. you can use the placeholder to represents the value you want to insert
3. could improve the efficiency
public void addUser(User user) { Connection connection = null; PreparedStatement pstatement = null; try { connection = JdbcUtil.getConnection();
// prepare the statement and we can see the placeholder in the sql statement pstatement = connection .prepareStatement("INSERT INTO user(username,password,email,birthday) VALUES(?,?,?,?);");
// set the value of placeholder pstatement.setString(1, user.getUsername()); pstatement.setString(2, user.getPassword()); pstatement.setString(3, user.getEmail()); pstatement.setDate(4, new java.sql.Date(user.getBirthday() .getTime()));
// execute the sql statement and return it has ResultSet or not boolean flag = pstatement.execute(); } catch (SQLException e) { throw new RuntimeException(e); } finally { JdbcUtil.release(connection, pstatement, null); } }
Singelton Pattern & Use Singelton pattern to decoupling the service and dao layer:
package pp.com.util; import java.io.InputStream; import java.util.Properties; import pp.com.dao.UserDao; public class BeanFactory { private static BeanFactory instace = new BeanFactory(); private BeanFactory() { } public static BeanFactory getInstance() { return instace; } public UserDao getUserDao() { try { InputStream in = BeanFactory.class.getClassLoader() .getResourceAsStream("dao.properties"); Properties prop = new Properties(); prop.load(in); String userDao = prop.getProperty("userDao"); UserDao instance = (UserDao) Class.forName(userDao).newInstance(); return instance; } catch (Exception e) { throw new RuntimeException("Fail to create the instace of UserDao!"); } } }