This is the 27th day of my participation in Gwen Challenge

JDBC

The source code

Public static void main(String[] args) {
    Connection connection = null;
	PreparedStatement preparedStatement = null;
	ResultSet resultSet = null;
			
	try {
		// Load the database driver
		Class.forName("com.mysql.jdbc.Driver");
				
		// Get the database link from the driver management class
		connection =  DriverManager.getConnection("jdbc:mysql://localhost:3306/mybatis? characterEncoding=utf-8"."root"."mysql");
		// Define SQL statements? Represents a placeholder
		String sql = "select * from user where username = ?";
		// Get the preprocessed statement
		preparedStatement = connection.prepareStatement(sql);
		The first parameter is the sequence number (starting from 1) of the parameter in the SQL statement, and the second parameter is the value of the parameter
		preparedStatement.setString(1."Fifty");
		// Issue SQL to the database to execute the query and query the result set
		resultSet =  preparedStatement.executeQuery();
		// Iterate over the query result set
		while(resultSet.next()){
			System.out.println(resultSet.getString("id") +""+resultSet.getString("username")); }}catch (Exception e) {
				e.printStackTrace();
		}finally{
			// Release resources
		if(resultSet! =null) {try {
				resultSet.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch blocke.printStackTrace(); }}if(preparedStatement! =null) {try {
			preparedStatement.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch blocke.printStackTrace(); }}if(connection! =null) {try {
				connection.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch blocke.printStackTrace(); }}}}Copy the code

JDBC coding steps

Loading the database driver
Create and get database links
Create a JDBC Statement object
Setting SQL statements
Set parameters in SQL statements (using preparedStatement)
The statement executes the SQL and retrieves the results
Analyze and process the SQL execution results
Release resources (resultSet, PreparedStatement, Connection)

The problem of JDBC

The frequent creation and release of database links waste system resources and affect system performance. This problem can be solved by using database link pools.
Sql statements are hardcoded in the code, which makes the code difficult to maintain. As a result, Sql changes in actual applications may be large, and changes in Sql require changes in Java code.
Hard coding exists in transferring parameters to possession bit symbols in preparedStatement, because the WHERE conditions of SQL statements are not necessarily, which may be more or less. Modifying SQL requires modifying codes, making the system difficult to maintain.
There is hard coding (query column name) for result set parsing, SQL changes lead to parsing code changes, the system is not easy to maintain, if the database records can be encapsulated as POJO object parsing is more convenient.

MyBatis

The structure of the MyBatis

chart

First try mybatis add delete change check

demand
- Query user information based on the user ID (primary key)
- Fuzzy query user information by user name
- Add user
- Delete user
- Update user

process

Configure the sqlmapconfig. XML core configuration file


        
<! DOCTYPEconfiguration
PUBLIC "- / / mybatis.org//DTD Config / 3.0 / EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
	<! -- Environments will be abolished after integration with Spring
	<environments default="development">
		<environment id="development">
		<! -- Use JDBC transaction management -->
			<transactionManager type="JDBC" />
		<! -- Database connection pool -->
			<dataSource type="POOLED">
				<property name="driver" value="com.mysql.jdbc.Driver" />
				<property name="url" value="jdbc:mysql://localhost:3306/mybatis? characterEncoding=utf-8" />
				<property name="username" value="root" />
				<property name="password" value="mysql" />
			</dataSource>
		</environment>
	</environments>
	
</configuration>
   
Copy the code

Create the Po
- The Po class corresponds to the fields in the database one by one, and is used as mybatis for SQL mapping

Public class User {
	private int id;
	private String username;// User name
	private String sex;/ / gender
	private Date birthday;/ / birthday
	private String address;/ / addressGet/set...Copy the code

Create the mapping file user.xml
- Namespace: Namespace used to isolate SQL statements
- ParameterType: Defines the type of mapping input to SQL. #{ID} sets the placeholder symbol and passes the input variable ID to SQL using preparedStatement.
- ResultType: Defines the result mapping type.


        
<! DOCTYPEmapper
PUBLIC "- / / mybatis.org//DTD Mapper / 3.0 / EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="test">
<select id="findUserById" parameterType="int" resultType="cn.itcast.mybatis.po.User">
		select * from user where id = #{id}
	</select>
	<! Select * from user list by default
	<select id="findUserByUsername" parameterType="java.lang.String" 
			resultType="cn.itcast.mybatis.po.User">
	   select * from user where username like '%${value}%' 
	</select>

</mapper>
Copy the code

Load the mapping file in sqlmapconfig.xml

<mappers>
	<mapper resource="User.xml"/>
</mappers>
Copy the code

The test program

public class Mybatis_first {
	// Session factory
	private SqlSessionFactory sqlSessionFactory;

	@Before
	public void createSqlSessionFactory(a) throws IOException {
		// Config file
		String resource = "SqlMapConfig.xml";
		InputStream inputStream = Resources.getResourceAsStream(resource);

		// Use SqlSessionFactoryBuilder to create SqlSessionFactory from the XML configuration file
		sqlSessionFactory = new SqlSessionFactoryBuilder()
				.build(inputStream);

	}

	// Query user information by ID
	@Test
	public void testFindUserById(a) {
		// Database session instance
		SqlSession sqlSession = null;
		try {
			// Create database session instance sqlSession
			sqlSession = sqlSessionFactory.openSession();
			// Query a single record by user ID
			User user = sqlSession.selectOne("test.findUserById".10);
			// Output user information
			System.out.println(user);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if(sqlSession ! =null) { sqlSession.close(); }}}// Query user information based on the user name
	@Test
	public void testFindUserByUsername(a) {
		// Database session instance
		SqlSession sqlSession = null;
		try {
			// Create database session instance sqlSession
			sqlSession = sqlSessionFactory.openSession();
			// Query a single record by user ID
			List<User> list = sqlSession.selectList("test.findUserByUsername"."Zhang");
			System.out.println(list.size());
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if(sqlSession ! =null) { sqlSession.close(); }}}}Copy the code

# {} and ${}
1. # {}
  - #{} represents a placeholder, which allows preparedStatement to set values to, automatically converting Java and JDBC types
  - #{} effectively prevents SQL injection
  - #{} can accept simple type values or POJO attribute values
  - If parameterType transmits a single simple type value, the #{} parentheses can be value or some other name.
2. The ${}
  - {} is used to concatenate the contents of parameterType into SQL without JDBC type conversion
  - {} can accept either a simple type value or a POJO property value if parameterType transmits a single simple type value. If parameterType transmits a single simple type value, the {} parentheses must be value.
ParameterType and resultType
1. ParameterType: specifies input parameter types. Mybatis uses OGNL to get parameter values from the input object and splice them into SQL.
2. ResultType: specifies the output resultType. Mybatis maps a row of record data of the SQL query result to an object of the specified type.

Get User id (primary key after insert)

Prerequisite: used in insert statements
Some tables are associated, and the primary key needs to be inserted into another table as a foreign key

Mysql autoincrement primary key returns
- Adding a selectKey implementation returns the primary key
- KeyProperty: Which property in the POJO is the returned primary key stored
- Order: Selectkeys are executed in after order, as opposed to the insert statement. The primary key is generated after the insert statement
- ResultType: What type of primary key is returned
- LAST_INSERT_ID(): this is a mysql function that returns the id of a new auto_increment column.

<! Add user -->
<insert id="insertUser" parameterType="cn.sju.po.User">
	<selectKey keyProperty="id" order="AFTER" resultType="int">
		select Last_insert_id()
	</selectKey>
	insert into user(id,username,birthday,sex,address) value(#{id},#{username},#{birthday},#{sex},#{address}) 
</insert>
Copy the code

User user = new User();
	user.setUsername("Wang Xiaojun");
	user.setSex("Male");
	user.setBirthday(new Date());
	sqlSession.insert("test.insertUser",user);
	sqlSession.commit();
	// The output statement is valid only after the previous mapping file is set
	System.out.println(user.getId());
	/ / close
	sqlSession.close();
Copy the code

Mysql implements the primary key using uUID
- Get primary key ID after insert when primary key id is not increment
- Select uuid() to get the UUID value
- Note: The order used here is BEFORE

<insert  id="insertUser" parameterType="cn.itcast.mybatis.po.User">
<selectKey resultType="java.lang.String" order="BEFORE" 
keyProperty="id">
select uuid()
</selectKey>
insert into user(id,username,birthday,sex,address) 
		 values(#{id},#{username},#{birthday},#{sex},#{address})
</insert>
Copy the code

conclusion
1. Use it after inserting, updating, and deletingsqlSession.commit()Commit the transaction

mo4tech.com (Moment For Technology) is a global community with thousands techies from across the global hang out!Passionate technologists, be it gadget freaks, tech enthusiasts, coders, technopreneurs, or CIOs, you would find them all here.

Mybatis – curd operation

JDBC

The source code

JDBC coding steps

The problem of JDBC

MyBatis

The structure of the MyBatis

First try mybatis add delete change check

Mybatis – curd operation

JDBC

The source code

JDBC coding steps

The problem of JDBC

MyBatis

The structure of the MyBatis

First try mybatis add delete change check

Related Posts

Python json module

MySQL persistence guarantee – Redo logs

Principle and design of distributed cache Redis