I believe everyone is familiar with jdbc. As long as you are a java person, you have to learn such a thing when you first come into contact with j2ee. Who says that programs have to deal with databases? jdbc is a very basic knowledge to deal with databases, and is also relatively close to the underlying level. In actual work, people use more mature frameworks, such as Hibernate and Mybatis.
However, as the underlying jdbc of these mature frameworks, we should also master it. Only by understanding the addition, deletion, modification and query of jdbc, can we better understand how these mature frameworks implement addition, deletion, modification and query when they are interested in studying the source code of Hibernate or Mybatis in the future.
Going back to the topic, let’s take a look at our development environment:
Java language, Eclipse development tools, Mysql database, Navicat database visualization tools.
Please check the information yourself for the installation, construction and use of the development environment (very simple), and will not be explained in detail here.
The first step is to create a database, use the Navicat database visualization tool to create a database, create a table in the database, give several fields in the table (remember to give an id field, a unique primary key, a self-increment sequence), and then give two pieces of data at will to test the function, as shown in the figure:
The second step is to open the database (I hope everyone can tap this example by yourself, which will not take much time. Be familiar with how jdbc deals with the database, so I will show it in the figure). As shown in the figure:
The third step is to transform the DBUtil class to facilitate the acquisition of database connections at the dao layer. The code is as follows:
package com.czgo.db;import java.sql.Connection;import java.sql.DriverManager;import java.sql.SQLException;public class DBUtil{ private static final String URL = "jdbc:mysql://127.0.0.1:3306/imooc"; private static final String UNAME = "root"; private static final String PWD = "root"; private static Connection conn = null; static { try { // 1. Load the driver Class.forName("com.mysql.jdbc.Driver"); // 2. Obtain the connection to the database conn = DriverManager.getConnection(URL, UNAME, PWD); } catch (ClassNotFoundException e) { e.printStackTrace(); } catch (SQLException e) { e.printStackTrace(); } } public static Connection getConnection() { return conn; }} Step 4: Create entity classes (as shown in the picture above, everyone observes the allocation of packages. We will use MVC idea to design this example. There are design ideas about mvc, please learn it yourself, I won’t say much here) the code is as follows:
package com.czgo.model;import java.io.Serializable;/** * Entity class: goddess class* * @author AlanLee * */public class Goddess implements Serializable{ private static final long serialVersionUID = 1L; /** * Unique primary key*/ private Integer id; /** * Name*/ private String name; /** * Mobile number*/ private String mobie; /** * Email*/ private String email; /** * Home address*/ private String address; public Integer getId() { return id; } public void setId(Integer id) { this.id = id; } public String getName() { return name; } public void setName(String name) { this.name = name; } public String getMobie() { return mobie; } public void setMobie(String mobie) { this.mobie = mobie; } public String getEmail() { return email; } public void setEmail(String email) { this.email = email; } public String getAddress() { return address; } public void setAddress(String address) { this.address = address; }} Step 5, implementation of the dao layer (here, since it is a small example, there is no writing interface for large projects in actual work, which is convenient for program maintenance and expansion). The code is as follows:
package com.czgo.dao;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import java.util.ArrayList;import java.util.List;import com.czgo.db.DBUtil;import com.czgo.model.Goddess;/** * Data layer processing class* * @author AlanLee * */public class GoddessDao{ /** * Query all goddesses* * @return * @throws SQLException */ public List<Goddess> query() throws SQLException { List<Goddess> goddessList = new ArrayList<Goddess>(); // Get the database connection Connection conn = DBUtil.getConnection(); StringBuilder sb = new StringBuilder(); sb.append("select id,name,mobie,email,address from goddess"); // Operate the database through the database connection to realize addition, deletion, modification and search PreparedStatement ptmt = conn.prepareStatement(sb.toString()); ResultSet rs = ptmt.executeQuery(); Goddess goddess = null; while (rs.next()) { goddess = new Goddess(); goddess.setId(rs.getInt("id")); goddess.setName(rs.getString("name")); goddess.setMobie(rs.getString("mobie")); goddess.setEmail(rs.getString("email")); goddess.setAddress(rs.getString("address")); goddess.setAddress(rs.getString("address")); goddessList.add(goddess); } return goddessList; } /** * Query single goddess* * @return * @throws SQLException */ public Goddess queryById(Integer id) throws SQLException { Goddess g = null; Connection conn = DBUtil.getConnection(); String sql = "" + " select * from imooc_goddess " + " where id=? "; PreparedStatement ptmt = conn.prepareStatement(sql); ptmt.setInt(1, id); ResultSet rs = ptmt.executeQuery(); while (rs.next()) { g = new Goddess(); g.setId(rs.getInt("id")); g.setName(rs.getString("name")); g.setMobie(rs.getString("mobie")); g.setEmail(rs.getString("email")); g.setAddress(rs.getString("address")); } return g; } /** * Add goddess* * @throws SQLException */ public void addGoddess(Goddess goddess) throws SQLException { // Get the database connection Connection conn = DBUtil.getConnection(); String sql = "insert into goddess(name,mobie,email,address) values(?,?,?,?)"; PreparedStatement ptmt = conn.prepareStatement(sql); ptmt.setString(1, goddess.getName()); ptmt.setString(2, goddess.getMobie()); ptmt.setString(3, goddess.getEmail()); ptmt.setString(4, goddess.getAddress()); ptmt.execute(); } /** * Modify the goddess information* * @throws SQLException */ public void updateGoddess(Goddess goddess) throws SQLException { Connection conn = DBUtil.getConnection(); String sql = "update goddess set name=?,mobie=?,email=?,address=? where id=?"; PreparedStatement ptmt = conn.prepareStatement(sql); ptmt.setString(1, goddess.getName()); ptmt.setString(2, goddess.getMobie()); ptmt.setString(3, goddess.getEmail()); ptmt.setString(4, goddess.getAddress()); ptmt.execute(); } /** * Delete Goddess* * @throws SQLException */ public void deleteGoddess(Integer id) throws SQLException { Connection conn = DBUtil.getConnection(); String sql = "delete from goddess where id=?"; PreparedStatement ptmt = conn.prepareStatement(sql); ptmt.setInt(1, id); ptmt.execute(); }} Step 6: The implementation of the control layer (the control layer is used to imitate the control layer and interface here, and directly build data here. If it is the data of the interface, you can pass the receiving parameters through requests. You can change and improve the code of the control layer according to the actual situation. Here is just a simple test for you, and the time is tight, I hope you can understand it). The code is as follows:
package com.czgo.action;import java.sql.SQLException;import java.util.List;import com.czgo.dao.GoddessDao;import com.czgo.model.Goddess;/** * Control layer, directly build data here, and the data of the interface can be received through requests. The same is true * * @author AlanLee * */public class GoddessAction{ /** * Added goddess* * @param goddess * @throws Exception */ public void add(Goddess goddess) throws Exception { GoddessDao dao = new GoddessDao(); goddess.setName("Aoi Sora"); goddess.setMobie("52220000"); goddess.setEmail("[email protected]"); goddess.setAddress("Beijing Red Light District"); dao.addGoddess(goddess); } /** * Query single goddess* * @param id * @return * @throws SQLException */ public Goddess get(Integer id) throws SQLException { GoddessDao dao = new GoddessDao(); return dao.queryById(id); } /** * Modify Goddess* * @param goddess * @throws Exception */ public void edit(Goddess goddess) throws Exception { GoddessDao dao = new GoddessDao(); dao.updateGoddess(goddess); } /** * Delete Goddess* * @param id * @throws SQLException */ public void del(Integer id) throws SQLException { GoddessDao dao = new GoddessDao(); dao.deleteGoddess(id); } /** * Query all goddesses* * @return * @throws Exception */ public List<Goddess> query() throws Exception { GoddessDao dao = new GoddessDao(); return dao.query(); } /** * Test whether it is successful* * @param args * @throws SQLException */ public static void main(String[] args) throws SQLException { GoddessDao goddessDao = new GoddessDao(); List<Goddess> goddessList = goddessDao.query(); for (Goddess goddess : goddessList) { System.out.println(goddess.getName() + "," + goddess.getMobie() + "," + goddess.getEmail()); } }} Finally, let's see if the main method runs successfully:
In this way, a simple java jdbc connection to mysql database is completed to add, delete, modify and query. You can try to do an advanced query based on the query, that is, multi-condition query to consolidate the use of jdbc.
The above is all the content of this article. I hope it will be helpful to everyone's learning and I hope everyone will support Wulin.com more.