Learning content: learning JDBC (Day20)
1. Dao mode
2. DBhelper class
3. RowMapper interface
4. Inner class
1. Dao mode
(1) Dao layer: Data Access Object. Each Dao encapsulates CRUD operations on an entity class. Dao layer is the data access layer.
(2) Create Daos that operate on different entity classes in Dao package, and call the methods defined in UserDao to operate the data in User table.
public class UserDao {
private static final String DRIVER = "com.mysql.jdbc.Driver";
private static final String URL = "jdbc:mysql:///db_13?characterEncoding=utf8";
private static final String USER_NAME = "root";
private static final String PASSWORD = "root";
public void saveUser(User user) {
Connection conn = null;
PreparedStatement stat = null;
try {
Class.forName(DRIVER);
conn = DriverManager.getConnection(URL);
String sql = "insert into t_user (username,password,userage) values(?,?,?)";
stat = conn.prepareStatement(sql);
stat.setString(1, user.getUserName());
stat.setString(2, user.getPassword());
stat.setInt(3, user.getUserAge());
stat.executeUpdate();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
try {
if (stat != null) {
stat.close();
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}
try {
if (conn != null) {
conn.close();
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
} 2. DBHelper class
(1) DBHelper class is a common tool class, which encapsulates common additions, deletions, modifications and queries, reduces a large amount of redundant code and simplifies development.
When a system has to operate data in multiple tables, create a Dao class for every entity class, and rewrite the operation data method of the corresponding table in Dao, a lot of redundant code will appear. Therefore, these basic additions and deletions and rechecking methods are encapsulated in a tool class DBHelper, and the method of calling the tool class in Dao class will be simpler.
Define DBHelper object in Dao class and call tool class method:
public class UserDao {
private DBHelp dbHelp = new DBHelp();
public void saveUser(User user) {
String sql = "insert into t_user(username,password,userage) values(?,?,?)";
dbHelp.executeUpdate(sql,user.getUserName(),user.getPassword(),user.getUserAge());
}
public void deleteUserById(int id) {
String sql = "delete from t_user where id = ?";
dbHelp.executeUpdate(sql,id);
}
public void updateUserById(User user) {
String sql = "update t_user set username = ?,userage = ?,password = ? where id = ?";
dbHelp.executeUpdate(sql,user.getUserName(),user.getUserAge(),user.getPassword(),user.getId());
}
}Define the general addition, deletion, modification and query methods in the tool class DBHelper:
public class DBHelp {
private static final String DRIVER = "com.mysql.jdbc.Driver";
private static final String URL = "jdbc:mysql:///db_13?characterEncoding=utf8";
private static final String USER_NAME = "root";
private static final String PASSWORD = "root";
//When passing parameters, an indefinite item parameter is passed in. The type is Object
public void executeUpdate(String sql,Object... params){
Connection conn = null;
PreparedStatement stat = null;
try {
Class.forName(DRIVER);
conn = DriverManager.getConnection(URL, USER_NAME, PASSWORD);
stat = conn.prepareStatement(sql);
//Use the for loop to get the passed in parameters
for(int i = 0;i < params.length;i++){
stat.setObject(i + 1,params[i]);
}
stat.executeUpdate();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
try {
if (stat != null) {
stat.close();
}
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
try {
if (conn != null) {
conn.close();
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
}
}3. RowMapper interface
(1) The RowMapper interface is used to encapsulate each row of data in the data table into a user-defined class. In database query, if the returned type is a user-defined type, it needs to be encapsulated.
Define a mapperRow method in the RowMapper interface.
import java.sql.ResultSet;
import java.sql.SQLException;
public interface RowMapper {
//The defined method needs to throw an exception when overridden, so the method in the interface also needs to throw an exception.
Object mapperRow(ResultSet rs) throws SQLException;
}Create an internal class UserRowMapper in the UserDao class to implement the RowMapper interface.
private class UserRowMapper implements RowMapper {
@Override
public Object mapperRow(ResultSet rs) throws SQLException {
int id = rs.getInt("id");
String userName = rs.getString("username");
String password = rs.getString("password");
int age = rs.getInt("userage");
//Encapsulate the data in the data table into user-defined classes
User user = new User(id,userName,password,age);
return user;
}
}(2) Define a query method in Dao. If the query result does not need all the columns of the data table, you can define a local anonymous internal class in the defined method. Overload the mapperRow method in this local anonymous internal class to make its return value a new user object and encapsulate only the columns you want to find.
public class UserDao {
private DBHelp dbHelp = new DBHelp();
//Local anonymous inner class
public User findUserNameById(int id){
String sql = "select username from t_user where id = ?";
User user = (User)dbHelp.executeQueryForObject(sql, new RowMapper() {
@Override
public Object mapperRow(ResultSet rs) throws SQLException {
String userName = rs.getString("username");
User user = new User();
user.setUserName(userName);
return user;
}
}, id);
return user;
}
public User findUserById(int id) {
String sql = "select * from t_user where id = ?";
User user = (User) dbHelp.executeQueryForObject(sql, new UserRowMapper(), id);
return user;
}
public List<User> findAll() {
String sql = "select * from t_user";
List userList = dbHelp.executeQueryForList(sql,new UserRowMapper());
return userList;
}
}(3) Define the search method in DBHelp. When searching, use the indefinite item parameter to receive the characters to be received in the sql statement. When searching multiple rows of data, you can use the List collection to return multiple objects.
public Object executeQueryForObject(String sql, RowMapper rowMapper, Object... params) {
Connection conn = null;
PreparedStatement stat = null;
ResultSet rs = null;
Object obj = null;
try {
conn = getConn();
stat = conn.prepareStatement(sql);
for (int i = 0; i < params.length; i++) {
stat.setObject(i + 1, params[i]);
}
rs = stat.executeQuery();
if (rs.next()) {
obj = rowMapper.mapperRow(rs);
}
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
close(rs, stat, conn);
}
return obj;
}
public List executeQueryForList(String sql, RowMapper rowMapper, Object... params) {
Connection conn = null;
PreparedStatement stat = null;
ResultSet rs = null;
List objs = new ArrayList();
try {
conn = getConn();
stat = conn.prepareStatement(sql);
for (int i = 0; i < params.length; i++) {
stat.setObject(i + 1, params[i]);
}
rs = stat.executeQuery();
while (rs.next()) {
Object obj = rowMapper.mapperRow(rs);
objs.add(obj);
}
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
close(rs, stat, conn);
}
return objs;
}4. Inner class
(1) A class declared in a class is called an inner class. An inner class can access private instance variables in an outer class.
When the access modifier of the inner class is declared private, the inner class can only be used inside the outer class. When using the outer class, you do not know the existence of the inner class.
public class Outer {
public void hi() {
System.out.println("Hello,This is Outer Class");
}
public class Inner {
public void sayHello() {
System.out.println("Hello,This is Inner Class");
}
}
}(2) Instantiation of inner classes
Outer.Inner inner = new Outer().new Inner(); inner.sayHello();
Outer o = new Outer(); Inner inner = o.new Inner(); inner.sayHello();
(3) Use external class instance variables in internal classes:
1. When the external class and the internal class do not have variables with the same name, the external class instance variables can be used directly.
2. Use outer when the external class and internal class have variables with the same name this.name and this Name.
public class Outer {
private String name = "Tom";
public class Inner {
public void sayHello() {
System.out.println("Hello," + name);
}
}
}public class Outer {
private String name = "Tom";
public class Inner {
private String name = "Jerry";
public void sayHello() {
System.out.println("Hello," + Outer.this.name);
System.out.println("Hello," + this.name);
}
}
}(4) Create an inner class object inside an outer class
public class Outer {
private String name = "Tom";
public void hi() {
Inner inner = new Inner();
inner.age = 20;
inner.sayHello();
}
private class Inner {
private int age = 10;
public void sayHello() {
System.out.println("Hello," + Outer.this.name + "\t" + age);
}
}
}(5) Static inner class
1. calls in other classes
public class Outer {
public static class Inner {
public void say() {
System.out.println("Hello");
}
}
}
Outer.Inner inner = new Outer.Inner(); inner.say();
2. Directly call the static method of the external class in the static internal class
public class Outer {
public static String name = "Tom";
public static void hi() {
System.out.println("xixi");
}
public static class Inner {
public String name = "Jerry";
public void say() {
Outer.hi();
System.out.println("Hello"+Outer.name);
System.out.println("Hi!" + name);
}
}
}(6) Local inner class:
1. The local internal class is created in a method, and the scope of action is in this method.
2. The local internal class uses the variables defined in the method, and the variables must be declared final.
3. Anonymous local inner class (see 3.2 of this chapter)
Why use local inner classes:
1. To solve a complex problem, you want to create a class to assist your solution, but you don't want this class to be publicly available.
2. Implement an interface inside the method and return a reference to it.