MySQL Database Study Notes (12) ---- use of the open-source tool DbUtils (Database addition, deletion, modification, and query), mysqldbutils

MySQL Database Study Notes (12) ---- use of the open-source tool DbUtils (Database addition, deletion, modification, and query), mysqldbutils

[Statement] 

Reprinted, but keep the original source of the article → _ →

Life One: http://www.cnblogs.com/smyhvae/

Source: http://www.cnblogs.com/smyhvae/p/4085684.html

Contact: smyhvae@163.com

 

[Body]

This week's status is not very good, and it has been a few days in a row, so the blog has been interrupted for a week, and now it continues.

In previous articles, we learned how to add, delete, modify, and query databases using JDBC. In fact, in actual development, third-party tool classes are generally used. However, you can use open-source tools only when you have learned the basics. If you are not clear about the JDBC basics or do not understand this article, I suggest you review my previous articles related to "MySQL Database Study Notes. However, in any case, if the database is used for addition, deletion, modification, and query in the future, it must be the most used code in this article.

1. Introduction to DbUtils:

DBUtils is a small JDBC lightweight encapsulation tool kit under apache. Its core feature is the encapsulation of result sets, which can directly encapsulate the queried result sets into JavaBean, this is the most boring and error-prone part of our work.

: Http://commons.apache.org/proper/commons-dbutils/download_dbutils.cgi

The core class in DbUtils is the QueryRunner class.. Let's take a look at the core methods:

Update operation:

Runner. update ("delete from user where userName =? "," UserName "); int rowEffects = runner. update (" insert into user (userName, password, comment) values (?,?,?) "," Username "," password "," Remarks ");

Query operation:

// Return beanUser user = runner. query ("select * from user where userId =? ", 1, new BeanHandler <User> (User. class); // returns beanlistSystem. out. println ("returns the BeanList result ...... "); List <User> beanListResult = runner. query ("select * from user", new BeanListHandler (User. class); // return a value of Object increaseId = runner. query ("select last_insert_id ()", new ScalarHandler ());

 

Iii. Code implementation:

Next let's take a look at how DbUtils is used. Let's take a look at the file structure of the entire project:

That is, the DbUtils tool is used here to avoid writing a lot of code by yourself)

Test class: Test code availability.

The procedure is as follows:

First, create a database table: person. Field: id, name, age, description. The command for creating a table is as follows:

CREATE TABLE person(id int primary key auto_increment,name varchar(20),age int(2),description varchar(100)); 

Enter some simple data in the table for later query. The final result is as follows:

The mysql-connector-java-5.1.33-bin.jar and the downloaded commons-dbutils-1.6.jar are added to the Build path of the project. (If you do not want to download it from the official website, you can find it in the project file at the end of this Article)

(1) create a newDBUtilsTool class: (package com. util. db)

1 package com. util. db; 2 3 import java. SQL. connection; 4 import java. SQL. driverManager; 5 import java. SQL. resultSet; 6 import java. SQL. SQLException; 7 import java. SQL. statement; 8 import java. util. resourceBundle; 9 10/** 11 * database operation tool class 12*13 * @ author lamp14 * 15 */16 public class DBUtils {17 18 // database connection address 19 public static String URL; 20 // USERNAME 21 public static String USERNAME; 22 // PASSWORD 23 public static String PASSWORD; 24 // mysql DRIVER Class 25 public static String DRIVER; 2627 private static ResourceBundle rb = ResourceBundle. getBundle ("com. util. db. db-config ");28 29 private DBUtils () {30} 31 32 // use a static block to load the driver 33 static {34 URL = rb. getString ("jdbc. url "); 35 USERNAME = rb. getString ("jdbc. username "); 36 PASSWORD = rb. getString ("jdbc. password "); 37 DRIVER = rb. getString ("jdbc. driver "); 38 try {39 Class. forName (DRIVER); 40} catch (ClassNotFoundException e) {41 e. printStackTrace (); 42} 43} 44 45 // define a method for getting database connections 46 public static Connection getConnection () {47 Conne Ction conn = null; 48 try {49 conn = DriverManager. getConnection (URL, USERNAME, PASSWORD); 50} catch (SQLException e) {51 e. printStackTrace (); 52 System. out. println ("failed to get Connection"); 53} 54 return conn; 55} 56 57 // close database Connection 58 public static void close (ResultSet rs, Statement stat, Connection conn) {59 try {60 if (rs! = Null) 61 rs. close (); 62 if (stat! = Null) 63 stat. close (); 64 if (conn! = Null) 65 conn. close (); 66} catch (SQLException e) {67 e. printStackTrace (); 68} 69} 70 71}

Note: In line 27, check whether the package name of the property file is correct. This property file will be defined later.

Line 29: since it is a tool class, do not instantiate it. In this case, you can use the single-sample design mode or private the constructor.

Line 27: we can see that we write the URL, user name, password, and other information of the database to an attribute file (jdbc. properties). We will define this attribute file later.

Row 32: to avoid repeated code, use static code blocks: the code is executed only once during class loading.

Row 45: Define a method for getting database connections

Row 57: Close database connection

(2) create an attribute file, new --> file, and name it:Db-config.propertiesThe Code is as follows:

jdbc.url=jdbc:mysql://localhost:3306/jdbcdbjdbc.username=rootjdbc.password=smyhjdbc.driver=com.mysql.jdbc.Driver

If you need to modify the configuration information in the future, you only need to modify it here. Note how the configuration information is called in the preceding DBUtils class.

(3) create a file and define the Person class: (package com. vae. domain)

 1 package com.vae.domain; 2  3 public class Person { 4     private int id; 5     private String name; 6     private int age; 7     private String description; 8  9     public int getId() {10         return id;11     }12 13     public void setId(int id) {14         this.id = id;15     }16 17     public String getName() {18         return name;19     }20 21     public void setName(String name) {22         this.name = name;23     }24 25     public int getAge() {26         return age;27     }28 29     public void setAge(int age) {30         this.age = age;31     }32 33     public String getDescription() {34         return description;35     }36 37     public void setDescription(String description) {38         this.description = description;39     }40 41     public Person(int id, String name, int age, String description) {42         super();43         this.id = id;44         this.name = name;45         this.age = age;46         this.description = description;47     }48 49     public Person(String name, int age, String description) {50         super();51         this.name = name;52         this.age = age;53         this.description = description;54     }55 56     public Person() {57         super();58         // TODO Auto-generated constructor stub59     }60 61     @Override62     public String toString() {63         return "Person [id=" + id + ", name=" + name + ", age=" + age64                 + ", description=" + description + "]";65     }66 67 }

This Person class is a domain model, indicating to add, delete, modify, and query it.

Then definePersonDao Interface: A special interface (package com. vae. dao) for operations on the Person class (for example, adding, deleting, modifying, and querying)

Note: It is a definition interface, not a definition class. The Code is as follows:

1 package com. vae. dao; 2 3 import java. SQL. SQLException; 4 import java. util. list; 5 6 import com. vae. domain. person; 7 8 public interface PersonDao {9 // add Method 10 public void add (Person p) throws SQLException; 11 12 // update Method 13 public void update (Person p) throws SQLException; 14 15 // deletion method 16 public void delete (int id) throws SQLException; 17 18 // query method 19 public Person findById (int id) throws SQLException; 20 21 // find all 22 public lists <Person> findAll () throws SQLException; 23 24 // query several records 25 public long personCount () throws SQLException; 26 27}

(4) then, definePeronDaoImpl implementation classTo implement the preceding PeronDao interface (package com. vae. dao)

1 package com. vae. dao; 2 3 import java. SQL. SQLException; 4 import java. util. list; 5 6 import org. apache. commons. dbutils. queryRunner; 7 import org. apache. commons. dbutils. handlers. beanHandler; 8 import org. apache. commons. dbutils. handlers. beanListHandler; 9 import org. apache. commons. dbutils. handlers. scalarHandler; 10 11 import com. util. db. DBUtils; 12 import com. vae. domain. person; 13 14 public class Perso NDaoImpl implements PersonDao {15 private QueryRunner runner = null; // query runner 16 public PersonDaoImpl () {17 runner = new QueryRunner (); 18} 19 20 // method: add a record 21 @ Override22 public void add (Person p) throws SQLException {23 String SQL = "insert into person (name, age, description) to the database) values (?,?,?) "; 24 runner. update (DBUtils. getConnection (), SQL, p. getName (), p. getAge (), p. getDescription (); 25} 26 27 // method: modify a record 28 @ Override29 public void update (Person p) to the database based on the id) throws SQLException {30 String SQL = "update person set name = ?, Age = ?, Description =? Where id =? "; 31 runner. update (DBUtils. getConnection (), SQL, p. getName (), p. getAge (), p. getDescription (), p. getId (); 32} 33 34 // method: delete a record in the database by id 35 @ Override36 public void delete (int id) throws SQLException {37 String SQL = "delete from person where id =? "; 38 runner. update (DBUtils. getConnection (), SQL, id); 39} 40 41 42 // method: Use BeanHandler to query an object 43 @ Override44 public Person findById (int id) throws SQLException {45 String SQL = "select name, age, description from person where id =? "; 46 Person p = runner. query (DBUtils. getConnection (), SQL, new BeanHandler <Person> (Person. class), id); 47 return p; 48} 49 50 // method: Use BeanListHandler to query all objects 51 @ Override52 public List <Person> findAll () throws SQLException {53 String SQL = "select name, age, description from person"; 54 List <Person> persons = runner. query (DBUtils. getConnection (), SQL, new BeanListHandler <Person> (Person. class); 55 return persons; 56} 57 58 // method: Use ScalarHandler to query a total of several records 59 @ Override60 public long personCount () throws SQLException {61 String SQL = "select count (id) from person"; 62 return runner. query (DBUtils. getConnection (), SQL, new ScalarHandler <Long> (); 63} 64 65}

Core code: Lines 15, 17, 24, 31, 38, 46, 54, and 62.

(5) create a test classTest. java(Package com. vae. test)

1 package com. vae. test; 2 3 import java. SQL. SQLException; 4 import java. util. list; 5 6 import com. vae. dao. personDao; 7 import com. vae. dao. personDaoImpl; 8 import com. vae. domain. person; 9 10 public class Test {11 12 public static void main (String [] args) throws SQLException {13 PersonDao dao = new PersonDaoImpl (); 14 15 // dao. add (new Person ("Life Cycle Number", 22, "Records added through Java commands"); 16 17 // dao. update (new Person (1, "Life 1", 23, "records modified by using Java commands"); 18 19 // dao. delete (4); 20 21 // Person p = dao. findById (1); 22 // System. out. println (p); 23 24 // List <Person> persons = dao. findAll (); 25 // System. out. println (persons); 26 27 long count = dao. personCount (); 28 System. out. println (count); 29} 30 31}

After testing, the above 15 to 28 lines of code can be run.

For example, when code 21st to 22 is executed, the background output is as follows:

When code 24th to 25 is executed, the background output is as follows:

When code 27th to 28 is executed, the background output is as follows:

[Engineering documents]

Link: http://pan.baidu.com/s/1qWqKreO

Password: 9wed