History is a mirror
Looking back on my own development and witnessing the history of times, let me talk about how java connects to databases
0 native jdbc
Get started with jdbc, so people who are afraid of new entrants will already be immersed in packaging libraries and frameworks without even using native jdbc.
Java database connection, full name is Java Database Connectivity, short for JDBC, is J The ava language is an application interface that specifies how client programs access the database, providing methods such as querying and updating data in the database.JDBC is also a trademark of Sun Microsystems.JDBC is commonly referred to as relational database oriented.Encyclopedia explains this Java Database Connection.
A few steps for developers:
Development steps:
1. Register driver. Tell JVM which database driver to use.
2. Get a connection. Use the classes in JDBC to complete the connection to the MySQL database
3. Obtain statement execution platform, get executor object of SQL statement through connection object
4. Execute the SQL statement, use the executor object, execute the SQL statement to the database, get the result after the execution of the database
5. Processing results
6. Release resources.
Note that before writing code, import database driver packages and connect databases from different vendors using different driver packages
Corresponding driver packages
New project, ordinary java project on the line, importing third-party jar is too simple, Baidu itself
The sample code is as follows:
import java.io.BufferedInputStream; import java.io.FileInputStream; import java.io.FileNotFoundException; 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.HashMap; import java.util.Iterator; import java.util.Properties; import com.mysql.jdbc.ResultSetMetaData; /** * * @author dgm * @describe "Native jdbc" * @date 2020 April 13, 2001 */ public class MysqlTest { // JDBC driver name and database URL static final String JDBC_DRIVER = "com.mysql.jdbc.Driver"; static final String DB_URL = "jdbc:mysql://192.168.8.200:3306/bdrackdemo?useUnicode=true&characterEncoding=utf8&autoReconnect=true"; // User name and password of the database, depending on your settings static final String USER = "root"; static final String PASS = "cstorfs"; static Properties prop = new Properties(); //Read Database Configuration File static void readDBSetting(String path) { // Properties prop = new Properties(); // Read the properties file mysql.properties InputStream in = null; try { in = new BufferedInputStream(new FileInputStream(path)); } catch (FileNotFoundException e) { // TODO Auto-generated catch block e.printStackTrace(); } try { prop.load(in); } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); } // /Load Property List Iterator<String> it = prop.stringPropertyNames().iterator(); while (it.hasNext()) { String key = it.next(); System.out.println(key + "=" + prop.getProperty(key)); } try { in.close(); } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); } // return prop; } public static void main(String[] args) { // Read mysql configuration information readDBSetting("conf/mysql.properties"); Connection conn = null; Statement stmt = null; ResultSet rs = null; try { // Register JDBC drivers Class.forName(prop.getProperty("dbDriver")).newInstance(); // Open Link System.out.println("Connect to database..."); conn = DriverManager .getConnection( "jdbc:mysql://" + prop.getProperty("mysqlhost") + ":" + prop.getProperty("mysqlport") + "/" + prop.getProperty("dbname") + "?useUnicode=true&characterEncoding=utf8&autoReconnect=true", prop.getProperty("mysqluser"), prop.getProperty("mysqlpwd")); // Execute Query System.out.println(" instantiation Statement object..."); stmt = conn.createStatement(); String sql = "SELECT id, username, number FROM student"; rs = stmt.executeQuery(sql); // Expand Result Set Database while (rs.next()) { // output data System.out.print("user id: " + rs.getInt("id")); System.out.print(", User name: " + rs.getString("username")); System.out.print(", School Number: " + rs.getString("number")); System.out.print("\n"); } } catch (SQLException se) { // Handling JDBC errors se.printStackTrace(); } catch (Exception e) { // Handling Class.forName errors e.printStackTrace(); } finally { // close resource try { if (rs != null) rs.close(); } catch (SQLException se3) { }// Don't do anything? // close resource try { if (stmt != null) stmt.close(); } catch (SQLException se2) { }// Don't do anything? try { if (conn != null) conn.close(); } catch (SQLException se) { se.printStackTrace(); } } } }
Query Results
That's what early development of En did, just encapsulated itself as a JDBC tool, for specific business developers to write a lot of SQL statements, when called
It seems that this kind of development mode is also very good, as long as the scale is small, this method is still good, the bottleneck is the database connection, now it is not a problem, society is progressing, pooling technology appears.
Pooling is simply about pre-connecting a certain number of connections and choosing one to operate on when needed.Omitted, tomcat's own pooling technology was the main use at that time, and the code to get a database connection became such a core pseudocode (tomcat needs some configuration):
//Constructor public DataAccess(String poolName) { this.poolName = poolName; this.JNDI = "java:comp/env/" + poolName; } //Get Database Connection private void setConnection() throws Exception { Config config = Config.getConfig(); if ( config.getWebserver().equals("tomcat") ) { Context ctx = new InitialContext(); DataSource ds = (DataSource)ctx.lookup(JNDI); //Get Database Connection this.conn = ds.getConnection(); } else { //Other server s } }
JNDI under Popularity https://baike.baidu.com/item/JNDI/3792442?fr=aladdin And now it's hard to see that many frames are getting more and more foolish
1. hibernate is here
Hibernate is an ORM framework, all known as Object_Relative DateBase-Mapping, that establishes a mapping between Java objects and relational databases for direct access to Java objects.
I don't know why mybatis isn't growing so well. It seems important to bring together technicians to build communities.
Unfortunately, there are no old projects left, no computers of your own, and no github applications.
I have seen many tutorials on the Internet: https://www.w3cschool.cn/hibernate/skzl1idz.html Instead of saying much, focus on architecture, caching, transactions, and interceptors.Key Codes
//Get Load Configuration Management Class Configuration configuration = new Configuration(); //The hibernate.cfg.xml file is loaded by default without parameters. configuration.configure(); //Create Session Factory Object SessionFactory factory = configuration.buildSessionFactory(); //Get Session Object Session session = factory.openSession(); //When you use Hibernate to manipulate a database, you open a transaction to get the transaction object Transaction transaction = session.getTransaction(); //Open Transaction transaction.begin(); //Add Objects to Database //session.save(user); //Database operation omitted //Submit Transaction transaction.commit(); //Close Session session.close();
2. mybatis is coming too, more aggressively
It will take a little longer. There is no way to make it grow well. There is strong support from the big factories.
Introduction to mybatis https://baike.baidu.com/item/MyBatis/2824918?fr=aladdin Is a competitor for hibernate
I see similar tutorials available online, MyBatis tutorial https://www.w3cschool.cn/mybatis/,
Focus also on architecture, caching, and transactions
Simple demo is as follows:
package spring.dao public interface MybatisDao { List<HashMap> selectUser(); } public class MybatisDaoImpl implements MybatisDao { public SqlSession sqlSession; public MybatisDaoImpl(SqlSession sqlSession) { this.sqlSession = sqlSession; } @Override public List<HashMap> selectUser() { // TODO Auto-generated method stub return this.sqlSession.selectList("spring.dao.MybatisDao.selectUser"); } } //Test Code public class MybatisTest { public static void main(String[] args) { // TODO Auto-generated method stub try { System.out.println("start mybatis Experiment"); MybatisDao userDao; SqlSession sqlSession; String resource = "conf/mybatis-config.xml"; InputStream inputStream = Resources.getResourceAsStream(resource); SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder() .build(inputStream); sqlSession = sqlSessionFactory.openSession(); userDao = new MybatisDaoImpl(sqlSession); List<HashMap> userList = userDao.selectUser(); for (HashMap user : userList) { System.out.println(user); } sqlSession.close(); System.out.println("End mybatis Experiment"); } catch (IOException e) { e.printStackTrace(); } catch (Exception e) { e.printStackTrace(); } } }
The three profiles are as follows
mybatis-config.xml
<?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd"> <configuration> <properties resource="conf/mysql.properties" /> <environments default="development"> <environment id="development"> <transactionManager type="JDBC" /> <dataSource type="POOLED"> <property name="driver" value="${mybatis.driver}" /> <property name="url" value="${mybatis.url}" /> <property name="username" value="${mybatis.username}" /> <property name="password" value="${mybatis.password}" /> </dataSource> </environment> </environments> <mappers> <mapper resource="conf/mybatisMapper.xml" /> </mappers> </configuration>
Contents of myBatisMapper.xml file
<?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> <!-- mapper:Root tag, namespace: Namespaces, generally guaranteed to be unique --> <mapper namespace="spring.dao.MybatisDao"> <!-- statement，Content: sql Sentence. id: Unique Identity, keep unique under the same namespace resultType: sql Encapsulation type of statement query result set --> <select id="selectUser" resultType="java.util.Map"> SELECT id, username, number FROM student </select> </mapper>
Database configuration mysql.properties
#MySQL for mybatis mybatis.driver=com.mysql.jdbc.Driver mybatis.url=jdbc:mysql://192.168.8.200:3306/bdrackdemo?useUnicode=true&characterEncoding=utf8&autoReconnect=true mybatis.username=root mybatis.password=cstorfs
Execute test code effect:
Note that the configuration file cannot be misplaced, or load the code for the property file instead, remember to import the database driver package and mybatis.jar
There are two kinds of caches, one-level cache and two-level cache.
There are several cases of first-level caching:
First case: the same session is queried twice
Conclusion: MyBatis only makes one database query.
Second case: two different queries on the same session
Conclusion: MyBatis makes two database queries.
Third: different session s, the same query.
Conclusion: MyBatis makes two database queries.
Fourth case: same session, update data after querying, query the same statement again
The direct conclusion is that the cache will be cleared after the update operation
Secondary Cache
The term "second-level cache" is relative to "first-level cache".Now that you have a first-level cache, why provide a second-level cache?We know that in the first level cache, when different session s make the same SQL query, they query the database twice.Obviously, this is a waste, since the SQL queries are the same, there is no need to look up the database again, just use the cached data directly, which is the original idea of MyBatis secondary caching.
In addition, when Spring and MyBatis are integrated, sqlsession is closed after each query, and data is emptied after closing.So once MyBatis and Spring are integrated, first-level caching is meaningless.If the secondary cache is turned on and the sqlsession is turned off, the data from the sqlsession first-level cache is added to the secondary cache of the mapper namespace.This way, the cache still exists after the sqlsession is closed.
By default, MyBatis only enables local session caching, which only caches data in one session, see: Introduction to MyBatis Level 1 Cache .To enable global secondary caching, you only need to add one line to the SQL map file:
<cache/>
A secondary cache is a Mapper-level cache. Multiple SqlSessions operate on the same Mapper's sql statement. Multiple SqlSessions can share a secondary cache, which is cross-SqlSessions.
The effect of this simple statement is as follows:
The results of all select statements in the mapping statement file will be cached.
All insert, update, and delete statements in the mapping statement file refresh the cache.
The cache uses the Least Recently Used algorithm (LRU) to clean up unneeded caches.
The cache does not refresh regularly (that is, there is no refresh interval).
The cache holds 1024 references to a list or object.
The cache is treated as a read/write cache, which means that the acquired objects are not shared and can be safely modified by the caller without interfering with potential modifications made by other callers or threads.
These properties can be modified through the properties of the cache element.For example:
<cache eviction="FIFO" flushInterval="60000" size="512" readOnly="true"/>
This more advanced configuration creates a FIFO cache that refreshes every 60 seconds, stores up to 512 references to result objects or lists, and returns objects that are considered read-only, so modifying them may conflict with callers in different threads.
Available cleanup strategies are:
LRU - least recently used: Remove objects that have not been used for the longest time.
FIFO - FIFO: Remove objects in the order they enter the cache.
SOFT - Soft Reference: Remove objects based on garbage collector status and soft reference rules.
WEAK - Weak References: Remove objects more actively based on garbage collector status and weak reference rules.
The default cleanup policy is LRU.
The flushInterval property can be set to any positive integer and should be set to a reasonable amount of time in milliseconds.By default, there is no refresh interval, and the cache only refreshes when the statement is called.
The size (number of references) property can be set to any positive integer, noting the size of the object you want to cache and the memory resources available in the running environment.The default value is 1024.
The readOnly property can be set to true or false.A read-only cache returns the same instance of the cached object to all callers.Therefore, these objects cannot be modified.This provides significant performance improvements.Read-write caches return (through serialization) copies of cached objects.It is slower but safer, so the default value is false.
Tip: The secondary cache is transactional.This means that the cache gets updated when the SqlSession completes and commits or completes and rolls back without executing the insert/delete/update statement for flushCache=true.
The first few parts of the framework are property file parsing, building factories, producing products and doing real things
Remember the sorting problem that arises when using mybatis once: ${} versus #{}
Because at the beginning of the sort field, especially in paging queries, the parameters passed are what page, how many records per page, the sort field is set dynamically, and sometimes from the front, sorted by certain fields
/** * Student Query Test Task List * @param user * @param pageSize * @param pageNumber * @param status,Test Paper Status (0-N, 1. Unpublished, Editable, Modified and Deleted, 2. Published, Viewable and Disabled, 3. Ended, Reviewable, default 0 represents all status) * @param name, Test Name Ambiguous Query * @param sortOrder, sort field * @return */ @SuppressWarnings({ "rawtypes", "unchecked" }) @RequestMapping(value = "/list", method = RequestMethod.GET, produces = "application/json; charset=utf-8") @LoginRequired @CrossOrigin public JSONObject list(@CurrentUser User user, @RequestParam(value = "pageSize", required = false, defaultValue = "10")Integer pageSize, @RequestParam(value = "pageNumber", required = false, defaultValue = "1")Integer pageNumber, Integer status, @RequestParam(value = "name", required = false, defaultValue = "") String name, @RequestParam(value = "sortOrder", required = false, defaultValue = " cp.id desc ") String sortOrder)
This is the sortOrder
That's about it, and everyone can expand their development.Framework iteration is always the same. xml to json and sql are written in xml files and now implemented by annotations (spring is also the same, xml was heavily configured in the early days, and now is converted to annotation configuration). The framework is becoming more and more packaged. I hope every yard farmer will learn the basics well and learn the framework again. Don't start with mybatis. It is said that the new yard farmer has never written a native servlet, let alone developed one.....
Summary:
jdbc components encapsulated in his early years are simple and easy to use, suitable for small-scale development, mainly due to limited technology at that time, a large number of database connections are not handled properly, there is no pooling and caching strategy, and the SQL foundation of Yanong is very strong, in fact, it is oriented to SQL programming
hibernate, personally feel a good orm framework, divorced from some sql, but not well promoted
mybatis, which is similar in structure to hibernate, has a good ecosphere and a big factory halo.
In a word: the best orm can not be separated from the most basic JDBC, persuade people who enter the framework circle to lay a good foundation, each frame has its own merits, no best, as long as it is appropriate!!!
Reference resources:
0. Develop Java applications with Oracle Database https://www.oracle.com/database/technologies/appdev/jdbc.html
1. Programmer's SQL Golden Book https://github.com/dongguangming/java/blob/master/%E7%A8%8B%E5%BA%8F%E5%91%98%E7%9A%84SQL%E9%87%91%E5%85%B8.pdf
2. JNDI database connection pool configuration https://www.iteye.com/blog/xiaoliang330-978823
4. Introduction to the Java Mybatis Framework tutorial http://c.biancheng.net/mybatis/