Ibatis integration with Sqlite: Small databases also play a major role, ibatissqlite

Ibatis integration with Sqlite: Small databases also play a major role, ibatissqlite

I. Introduction

Ibatis introduction:

Ibatis is a database ORM similar to Hibernate (Object relationship ing, which converts a row of a database table to an object), but it is different from the Automated Hibernate, he is a semi-automatic ORM and needs to write SQL statements by yourself. Through the ORM framework, you can no longer load your own database driver and establish a connection...

Sqlite introduction:

This is a small database, which does not need to be installed or has only one data file (The disadvantage is that there is no encryption function ).

 

Introduction to Baidu: There are many simple tutorials. This article focuses on Ibatis and sqlite integration.

 

II. Environment Construction

1. Create a database file

We recommend that you create a DataBase file and table fields before creating a project. We recommend that you use the SQLite DataBase Browser tool.

3. Configuration File

1. Ibatis configuration file

1 <? Xml version = "1.0" encoding = "UTF-8"?> 2 <! DOCTYPE sqlMapConfig PUBLIC "-// ibatis.apache.org//DTD SQL Map Config 2.0 // EN" 3 "http://ibatis.apache.org/dtd/sql-map-config-2.dtd"> 4 <sqlMapConfig> 5 <! -- Use JDBC Transaction Management --> 6 <transactionManager type = "JDBC"> 7 <! -- Data source --> 8 <dataSource type = "SIMPLE"> 9 <property name = "JDBC. driver "value =" org. sqlite. JDBC "/> 10 <property name =" JDBC. connectionURL "value =" jdbc: sqlite: test. db "/> 11 <property name =" JDBC. username "value =" "/> 12 <property name =" JDBC. password "value =" "/> 13 </dataSource> 14 </transactionManager> 15 <! -- Object ing file --> 16 <sqlMap resource = "com/loadfate/domain/User. xml"/> 17 </sqlMapConfig>

JDBC. ConnectionURL is configured differently in different environments
I. In Java Project:

Jdbc: sqlite: test. db is under the project path.

Ii. Web project:

When the database file is in src: jdbc: sqlite: resource: upgradeserver. db
When the database file is in windows: jdbc: sqlite:/D:/upgradeserver. db
When the database file is in the linux path: jdbc: sqlite: // home/zwq/upgradeserver. db

 

2. mapper configuration file

1 <? Xml version = "1.0" encoding = "UTF-8"?> 2 <! DOCTYPE sqlMap PUBLIC "-// ibatis.apache.org//DTD SQL Map 2.0 // EN" 3 "http://ibatis.apache.org/dtd/sql-map-2.dtd"> 4 5 <sqlMap> 6 <! -- Alias --> 7 <typeAlias alias = "User" type = "com. loadfate. domain. User"/> 8 <! -- Query all users --> 9 <select id = "selectAllUser" resultClass = "User"> 10 select * from user11 </select> 12 <! -- Query by Id. If the parameter is int, use id value --> 13 <select id = "selectUserById" parameterClass = "int" resultClass = "User"> 14 select * from user where userid = # id #15 </select> 16 <! -- Add User --> 17 <insert id = "addUser" parameterClass = "user"> 18 insert into User (username, password) values (# username #, # password #) 19 </insert> 20 <! -- Delete user --> 21 <delete id = "deleteUserById" parameterClass = "int"> 22 delete from user where userid = # id #23 </delete> 24 <! -- Change User --> 25 <update id = "updateUser" parameterClass = "user"> 26 update User set username = # username #, password = # password # where userid = # userid #27 </update> 28 </sqlMap>

 

IV. Implementation Details

1. IbatisUtil

1 private static SqlMapClient sqlMapClient = null; 2 static {3 try {4 // load the configuration file 5 Reader reader = Resources. getResourceAsReader ("SqlMapConfig. xml "); 6 sqlMapClient = SqlMapClientBuilder. buildSqlMapClient (reader); 7 reader. close (); 8} catch (IOException e) {9 e. printStackTrace (); 10} 11} 12 13 private IbatisUtil () {14} 15 16 public static SqlMapClient getSqlMapClient () {17 return sqlMapClient; 18}

 

2. UserDao

1 SqlMapClient sqlMapClient = IbatisUtil. getSqlMapClient (); 2 3 // Add User 4 public void addUser (user User) {5 try {6 sqlMapClient. insert ("addUser", user); 7} catch (SQLException e) {8 e. printStackTrace (); 9} 10} 11 // update User 12 public void updateUser (user User) {13 try {14 sqlMapClient. update ("updateUser", user); 15} catch (SQLException e) {16 e. printStackTrace (); 17} 18} 19 // delete User 20 public void deleteUser (user User) {21 try {22 sqlMapClient. insert ("deleteUserById", user. getUserid (); 23} catch (SQLException e) {24 e. printStackTrace (); 25} 26} 27 28 // get all users 29 @ SuppressWarnings ("unchecked") 30 public List <User> getAllUsers () {31 List <User> users = null; 32 try {33 users = sqlMapClient. queryForList ("selectAllUser"); 34} catch (SQLException e) {35 e. printStackTrace (); 36} 37 return users; 38} 39 40 // get User 41 public User getUser (int userid) {42 user User = null by id; 43 try {44 user = (User) sqlMapClient. queryForObject ("selectUserById", userid); 45} catch (SQLException e) {46 e. printStackTrace (); 47} 48 return user; 49}

3. Test

 

1 public static void main (String [] args) {2 User user = new User (); 3 user. setUsername ("Zhang San"); 4 user. setPassword ("123456"); 5 UserDao userDao = new UserDao (); 6 userDao. addUser (user); 7 8 User user2 = userDao. getUser (1); 9 System. out. println (user2.getUsername (); 10}

 

V,

Http://pan.baidu.com/s/1eQzP6Zw

What is SQLITE database?

SQLite is a lightweight database. It is designed to be embedded and has been used in many embedded products. It occupies very low resources. In embedded devices, it may only take several hundred KB of memory. It supports mainstream operating systems such as Windows, Linux, and Unix, and can be combined with many programming languages, such as Tcl, PHP, Java, and ODBC interfaces, similar to Mysql and PostgreSQL, the two world-renowned open-source database management systems, the processing speed is faster than that of them.
Although SQLite is small, the supported SQL statements are not inferior to those of other open-source databases. The supported SQL statements include:
ATTACH DATABASE
BEGIN TRANSACTION
Comment
COMMIT TRANSACTION
COPY
CREATE INDEX
CREATE TABLE
CREATE TRIGGER
CREATE VIEW
DELETE
DETACH DATABASE
DROP INDEX
DROP TABLE
DROP TRIGGER
DROP VIEW
END TRANSACTION
EXPLAIN
Expression
INSERT
On conflict clause
PRAGMA
REPLACE
ROLLBACK TRANSACTION
SELECT
UPDATE
It also supports transaction processing. Some people also say that it is like Microsoft's Access. Sometimes it is a bit similar, but in fact they are quite different. For example, SQLite supports cross-platform, simple operations, and can directly create databases in many languages. Unlike Access, SQLite requires Office support. If you are a very small application or want to do embedded development without a suitable database system, you can now consider using SQLite. Currently, the latest version is 3.2.2. its official website is www.sqlite.org or www.sqlite.com.cn. You can obtain the source code and documentation on it. At the same time, because the database structure is simple and the system source code is not much, it is also suitable for professionals who want to study database system development.

SQLite database operations

SQLite Helper class, SQLite database operation Class Based on. net c # SQLite this exquisite small database, without installing software, you only need a System. Data. SQLite. DLL file to operate SQLite database. However, it is said that the function is very powerful. Summary: SQLite is an open-source database that has become increasingly popular. It is very small and widely used in various types of applications. SQLite is already the world's most widely deployed SQL database engine, used in countless desktop computer applications, as well as consumer electronics, such as mobile phones, handheld computers and MP3 players. The source code of SQLite is put in the public domain (that is, the public domain of WikiPedia. Start to use: Sqliteman, windows best sqlite gui client sqlite, sqliteman 7.72 M

SQLite Administrator 0.8.3.2 connector is a database engine-based management tool. ┊ sqlite SQLite 1.68 M
SQLite, a lightweight database other than access and mysql
Using System;
Using System. Collections. Generic;
Using System. Text;
Using System. Web;
Using System. Configuration;
Using System. Data;
Using System. Data. SQLite; namespace DAL
{
Public class Sqlite
{
/// <Summary>
/// Obtain the connection object
/// </Summary>
/// <Returns> </returns>
Public static SQLiteConnection GetSQLiteConnection ()
{
Return new SQLiteConnection ("Data Source =" + System. web. httpContext. current. server. mapPath (System. configuration. configurationManager. appSettings ["db"]. toString ()));
} Private static void PrepareCommand (SQLiteCommand cmd, SQLiteConnection conn, string plain text, params object [] p)
{If (conn. State! = ConnectionState. Open)
Conn. Open ();
Cmd. Parameters. Clear ();
Cmd. Connection = conn;
Cmd. CommandText = plain Text; cmd. CommandType = CommandType. Text;
Cmd. Command... the remaining full text>