Ibatis.net Practice

1, Configuration Ibatis

References: Importing Ibatis references with NuGet
Configuration:
New Sqlmap.config

<?xml version= "1.0" encoding= "Utf-8"?>
<sqlmapconfig xmlns= "Http://ibatis.apache.org/dataMapper"
xmlns:xsi= "Http://www.w3.org/2001/XMLSchema-instance" >

<!--BAsic Setting about configuration-->
<settings>
<setting usestatementnamespaces= "false"/>
<setting cachemodelsenabled= "true"/>
</settings>

<providers resource= "Providers.config"/>
<database>
<provider name= "sqlServer2008"/>
<datasource name= "SQL Server" connectionstring= "Data source=;d atabase=;user id=;p assword=;"/>
</database>
<!--introduce XML files--
<sqlMaps>
<sqlmap resource= "Maps/ygbmap.xml"/>
</sqlMaps>
</sqlMapConfig>

Configure Provider.config
<?xml version= "1.0" encoding= "Utf-8"?>
<providers
Xmlns= "Http://ibatis.apache.org/providers"
xmlns:xsi= "Http://www.w3.org/2001/XMLSchema-instance" >

<clear/>
<provider
Name= "sqlServer1.0"
description= "Microsoft SQL Server 7.0/2000, provider V1.0.3300.0 in framework. NET V1.0"
Enabled= "false"
Assemblyname= "System.Data, version=1.0.3300.0, Culture=neutral, publickeytoken=b77a5c561934e089" connectionClass= " System.Data.SqlClient.SqlConnection "
Commandclass= "System.Data.SqlClient.SqlCommand"
Parameterclass= "System.Data.SqlClient.SqlParameter"
Parameterdbtypeclass= "System.Data.SqlDbType"
Parameterdbtypeproperty= "SqlDbType"
Dataadapterclass= "System.Data.SqlClient.SqlDataAdapter"
Commandbuilderclass= "System.Data.SqlClient.SqlCommandBuilder"
Usepositionalparameters= "false"
Useparameterprefixinsql= "true"
Useparameterprefixinparameter= "true"
parameterprefix= "@"/>
<provider
Name= "sqlServer1.1"
description= "Microsoft SQL Server 7.0/2000, provider V1.0.5000.0 in framework. NET V1.1"
Enabled= "false"
Assemblyname= "System.Data, version=1.0.5000.0, Culture=neutral, publickeytoken=b77a5c561934e089" connectionClass= " System.Data.SqlClient.SqlConnection "
Commandclass= "System.Data.SqlClient.SqlCommand"
Parameterclass= "System.Data.SqlClient.SqlParameter"
Parameterdbtypeclass= "System.Data.SqlDbType"
Parameterdbtypeproperty= "SqlDbType"
Dataadapterclass= "System.Data.SqlClient.SqlDataAdapter"
Commandbuilderclass= "System.Data.SqlClient.SqlCommandBuilder"
Usepositionalparameters= "false"
Useparameterprefixinsql= "true"
Useparameterprefixinparameter= "true"
parameterprefix= "@"/>
<provider
Name= "sqlServer2.0"
Enabled= "false"
description= "Microsoft SQL Server 2005, provider V2.0.50215.0 in framework. NET V2.0"
Assemblyname= "System.Data, version=2.0.50215.0, culture=neutral, publickeytoken=b77a5c561934e089"
Connectionclass= "System.Data.SqlClient.SqlConnection"
Commandclass= "System.Data.SqlClient.SqlCommand"
Parameterclass= "System.Data.SqlClient.SqlParameter"
Parameterdbtypeclass= "System.Data.SqlDbType"
Parameterdbtypeproperty= "SqlDbType"
Dataadapterclass= "System.Data.SqlClient.SqlDataAdapter"
Commandbuilderclass= "System.Data.SqlClient.SqlCommandBuilder"
Usepositionalparameters = "false"
Useparameterprefixinsql = "true"
Useparameterprefixinparameter = "true"
parameterprefix= "@"
/>
<provider
Name= "sqlServer2008"
Enabled= "true"
Default= "true"
description= "Microsoft SQL Server, provider V4.0.0.0 in framework. NET V4.0"
Assemblyname= "System.Data, version=4.0.0.0, culture=neutral, publickeytoken=b77a5c561934e089"
Connectionclass= "System.Data.SqlClient.SqlConnection"
Commandclass= "System.Data.SqlClient.SqlCommand"
Parameterclass= "System.Data.SqlClient.SqlParameter"
Parameterdbtypeclass= "System.Data.SqlDbType"
Parameterdbtypeproperty= "SqlDbType"
Dataadapterclass= "System.Data.SqlClient.SqlDataAdapter"
Commandbuilderclass= "System.Data.SqlClient.SqlCommandBuilder"
Usepositionalparameters = "false"
Useparameterprefixinsql = "true"
Useparameterprefixinparameter = "true"
parameterprefix= "@"
Allowmars= "true"
/>
<provider name= "OleDb1.1"
description= "OLE DB, Provider V1.0.5000.0 in framework. NET V1.1"
Enabled= "false"
Assemblyname= "System.Data, version=1.0.5000.0, Culture=neutral, publickeytoken=b77a5c561934e089" connectionClass= " System.Data.OleDb.OleDbConnection "
Commandclass= "System.Data.OleDb.OleDbCommand"
Parameterclass= "System.Data.OleDb.OleDbParameter"
Parameterdbtypeclass= "System.Data.OleDb.OleDbType"
Parameterdbtypeproperty= "OleDbType"
Dataadapterclass= "System.Data.OleDb.OleDbDataAdapter"
Commandbuilderclass= "System.Data.OleDb.OleDbCommandBuilder"
Usepositionalparameters= "true"
Useparameterprefixinsql= "false"
Useparameterprefixinparameter= "false"
parameterprefix= ""/>
<provider
Name= "Odbc1.1"
Description= "ODBC, provider V1.0.5000.0 in framework. NET V1.1"
Enabled= "false"
Assemblyname= "System.Data, version=1.0.5000.0, Culture=neutral, publickeytoken=b77a5c561934e089" connectionClass= " System.Data.Odbc.OdbcConnection "
Commandclass= "System.Data.Odbc.OdbcCommand"
Parameterclass= "System.Data.Odbc.OdbcParameter"
Parameterdbtypeclass= "System.Data.Odbc.OdbcType"
Parameterdbtypeproperty= "OdbcType"
Dataadapterclass= "System.Data.Odbc.OdbcDataAdapter"
Commandbuilderclass= "System.Data.Odbc.OdbcCommandBuilder"
Usepositionalparameters= "true"
Useparameterprefixinsql= "false"
Useparameterprefixinparameter= "false"
parameterprefix= "@"/>
<provider
Name= "oracle9.2"
description= "Oracle, Oracle Provider V9.2.0.401"
Enabled= "false"
Assemblyname= "Oracle.dataaccess, version=9.2.0.401, culture=neutral, publickeytoken=89b483f429c47342" Connectionclass= "Oracle.DataAccess.Client.OracleConnection"
Commandclass= "Oracle.DataAccess.Client.OracleCommand"
Parameterclass= "Oracle.DataAccess.Client.OracleParameter"
Parameterdbtypeclass= "Oracle.DataAccess.Client.OracleDbType"
Parameterdbtypeproperty= "Oracledbtype"
Dataadapterclass= "Oracle.DataAccess.Client.OracleDataAdapter"
Commandbuilderclass= "Oracle.DataAccess.Client.OracleCommandBuilder"
Usepositionalparameters= "false"
Useparameterprefixinsql= "true"
Useparameterprefixinparameter= "false"
parameterprefix= ":"
Usederiveparameters= "false"/>
<provider
Name= "oracle10.1"
description= "Oracle, Oracle Provider V10.1.0.301"
Enabled= "false"
Assemblyname= "Oracle.dataaccess, version=10.1.0.301, culture=neutral, publickeytoken=89b483f429c47342" Connectionclass= "Oracle.DataAccess.Client.OracleConnection"
Commandclass= "Oracle.DataAccess.Client.OracleCommand"
Parameterclass= "Oracle.DataAccess.Client.OracleParameter"
Parameterdbtypeclass= "Oracle.DataAccess.Client.OracleDbType"
Parameterdbtypeproperty= "Oracledbtype"
Dataadapterclass= "Oracle.DataAccess.Client.OracleDataAdapter"
Commandbuilderclass= "Oracle.DataAccess.Client.OracleCommandBuilder"
Usepositionalparameters= "true"
Useparameterprefixinsql= "true"
Useparameterprefixinparameter= "true"
parameterprefix= ":"
Usederiveparameters= "false"/>
<provider
Name= "oracleClient1.0"
Description= "Oracle, Microsoft provider V1.0.5000.0"
Enabled= "true"
Assemblyname= "System.Data.OracleClient, version=1.0.5000.0, culture=neutral, publickeytoken=b77a5c561934e089" Connectionclass= "System.Data.OracleClient.OracleConnection"
Commandclass= "System.Data.OracleClient.OracleCommand"
Parameterclass= "System.Data.OracleClient.OracleParameter"
Parameterdbtypeclass= "System.Data.OracleClient.OracleType"
Parameterdbtypeproperty= "OracleType"
Dataadapterclass= "System.Data.OracleClient.OracleDataAdapter"
Commandbuilderclass= "System.Data.OracleClient.OracleCommandBuilder"
Usepositionalparameters= "false"
Useparameterprefixinsql= "true"
Useparameterprefixinparameter= "false"
parameterprefix= ":"/>
<provider
Name= "Bytefx"
Description= "MySQL, Bytefx provider V0.7.6.15073"
Enabled= "false"
Assemblyname= "Bytefx.mysqlclient, version=0.7.6.15073, culture=neutral, PUBLICKEYTOKEN=F2FEF6FED1732FC1" Connectionclass= "ByteFX.Data.MySqlClient.MySqlConnection"
Commandclass= "ByteFX.Data.MySqlClient.MySqlCommand"
Parameterclass= "ByteFX.Data.MySqlClient.MySqlParameter"
Parameterdbtypeclass= "ByteFX.Data.MySqlClient.MySqlDbType"
Parameterdbtypeproperty= "Mysqldbtype"
Dataadapterclass= "ByteFX.Data.MySqlClient.MySqlDataAdapter"
Commandbuilderclass= "ByteFX.Data.MySqlClient.MySqlCommandBuilder"
Usepositionalparameters= "false"
Useparameterprefixinsql= "true"
Useparameterprefixinparameter= "true"
parameterprefix= "@"/>
<provider
Name= "MYSQL"
description= "MySQL, MySQL provider V6.9.8.0"
Enabled= "false"
Assemblyname= "Mysql.data, version=6.9.8.0, Culture=neutral, publickeytoken=c5687fc88969c44d" connectionClass= " MySql.Data.MySqlClient.MySqlConnection "
Commandclass= "MySql.Data.MySqlClient.MySqlCommand"
Parameterclass= "MySql.Data.MySqlClient.MySqlParameter"
Parameterdbtypeclass= "MySql.Data.MySqlClient.MySqlDbType"
Parameterdbtypeproperty= "Mysqldbtype"
Dataadapterclass= "MySql.Data.MySqlClient.MySqlDataAdapter"
Commandbuilderclass= "MySql.Data.MySqlClient.MySqlCommandBuilder"
Usepositionalparameters= "false"
Useparameterprefixinsql= "true"
Useparameterprefixinparameter= "true"
Parameterprefix= "?" />
<provider name= "SQLite3"
Description= "SQLite, sqlite.net provider V0.21.1869.3794"
Enabled= "false"
Assemblyname= "Sqlite.net, version=0.21.1869.3794, culture=neutral, publickeytoken=c273bd375e695f9c"
Connectionclass= "Finisar.SQLite.SQLiteConnection"
Commandclass= "Finisar.SQLite.SQLiteCommand"
Parameterclass= "Finisar.SQLite.SQLiteParameter"
Parameterdbtypeclass= "System.Data.DbType, System.Data"
Parameterdbtypeproperty= "DbType"
Dataadapterclass= "Finisar.SQLite.SQLiteDataAdapter"
Commandbuilderclass= "Finisar.SQLite.SQLiteCommandBuilder"
Usepositionalparameters= "false"
Useparameterprefixinsql= "true"
Useparameterprefixinparameter= "true"
parameterprefix= "@"
Setdbparameterprecision= "false"
Setdbparameterscale= "false"/>
<provider
Name= "Firebird1.7"
Description= "Firebird, Firebird SQL. NET provider V1.7.0.33200"
Enabled= "false"
Assemblyname= "FirebirdSql.Data.Firebird, version=1.7.0.33200, culture=neutral, publickeytoken=fa843d180294369d" Connectionclass= "FirebirdSql.Data.Firebird.FbConnection"
Commandclass= "FirebirdSql.Data.Firebird.FbCommand"
Parameterclass= "FirebirdSql.Data.Firebird.FbParameter"
Parameterdbtypeclass= "FirebirdSql.Data.Firebird.FbDbType"
Parameterdbtypeproperty= "Fbdbtype"
Dataadapterclass= "FirebirdSql.Data.Firebird.FbDataAdapter"
Commandbuilderclass= "FirebirdSql.Data.Firebird.FbCommandBuilder"
Usepositionalparameters= "false"
Useparameterprefixinsql= "true"
Useparameterprefixinparameter= "true"
parameterprefix= "@"/>
<provider
Name= "PostgreSql0.7"
Description= "POSTGRESQL, Npgsql provider V0.7.0.0"
Enabled= "false"
Assemblyname= "Npgsql, version=0.7.0.0, culture=neutral, Publickeytoken=5d8b90d52f46fda7"
Connectionclass= "Npgsql.npgsqlconnection"
Commandclass= "Npgsql.npgsqlcommand"
Parameterclass= "Npgsql.npgsqlparameter"
Parameterdbtypeclass= "Npgsqltypes.npgsqldbtype"
Parameterdbtypeproperty= "Npgsqldbtype"
Dataadapterclass= "Npgsql.npgsqldataadapter"
Commandbuilderclass= "Npgsql.npgsqlcommandbuilder"
Usepositionalparameters= "false"
Useparameterprefixinsql= "true"
Useparameterprefixinparameter= "true"
parameterprefix= ":"/>
<provider
Name= "PostgreSql0.7.1"
Description= "POSTGRESQL, Npgsql provider V0.7.1.0"
Enabled= "false"
Assemblyname= "Npgsql, version=0.7.1.0, culture=neutral, Publickeytoken=5d8b90d52f46fda7"
Connectionclass= "Npgsql.npgsqlconnection"
Commandclass= "Npgsql.npgsqlcommand"
Parameterclass= "Npgsql.npgsqlparameter"
Parameterdbtypeclass= "Npgsqltypes.npgsqldbtype"
Parameterdbtypeproperty= "Npgsqldbtype"
Dataadapterclass= "Npgsql.npgsqldataadapter"
Commandbuilderclass= "Npgsql.npgsqlcommandbuilder"
Usepositionalparameters= "false"
Useparameterprefixinsql= "true"
Useparameterprefixinparameter= "true"
parameterprefix= ":"/>
<provider
Name= "iDb2.10"
description= "IBM DB2 Provider, V 10.0"
Enabled= "false"
Assemblyname= "IBM. Data.DB2.iSeries, Version=10.0.0.0,culture=neutral, Publickeytoken=9cdb2ebfb1f93a26, Custom=null "connectionclass= "IBM. Data.DB2.iSeries.iDB2Connection "
Commandclass= "IBM. Data.DB2.iSeries.iDB2Command "
Parameterclass= "IBM. Data.DB2.iSeries.iDB2Parameter "
Parameterdbtypeclass= "IBM. Data.DB2.iSeries.iDB2DbType "
Parameterdbtypeproperty= "Idb2dbtype"
Dataadapterclass= "IBM. Data.DB2.iSeries.iDB2DataAdapter "
Commandbuilderclass= "IBM. Data.DB2.iSeries.iDB2CommandBuilder "
Usepositionalparameters= "true"
Useparameterprefixinsql= "false"
Useparameterprefixinparameter= "false"
parameterprefix= ""/>
<provider
Name= "Informix"
description= "Informix NET Provider, 2.81.0.0"
Enabled= "false"
Assemblyname= "IBM.Data.Informix, version=2.81.0.0, culture=neutral, publickeytoken=7c307b91aa13d208"
Connectionclass= "IBM.Data.Informix.IfxConnection"
Commandclass= "IBM.Data.Informix.IfxCommand"
Parameterclass= "IBM.Data.Informix.IfxParameter"
Parameterdbtypeclass= "IBM.Data.Informix.IfxType"
Parameterdbtypeproperty= "Ifxtype"
Dataadapterclass= "IBM.Data.Informix.IfxDataAdapter"
Commandbuilderclass= "IBM.Data.Informix.IfxCommandBuilder"
Usepositionalparameters = "true"
Useparameterprefixinsql = "false"
Useparameterprefixinparameter = "false"
Usederiveparameters= "false"
/>
</providers>

Create a new corresponding XML file (Ygb.xml) to manipulate the database
<?xml version= "1.0" encoding= "Utf-8"?>
<sqlmap namespace= "schoolentity" xmlns= "http://ibatis.apache.org/mapping" xmlns:xsi= "http://www.w3.org/2001/ Xmlschema-instance ">
<alias>
<typealias alias= "Yg_b" type= "Schoolentity.yg_b, schoolentity"/>
</alias>
<!--Configure the cache model--
<cachemodel id= "Ygb-cache" type= "Oscache" readonly= "true" serialize= "true" >
<flushinterval hours= "/>"
<flushonexecute statement= "Insertygb"/>
<flushonexecute statement= "Updateygb"/>
<flushonexecute statement= "Deleteygb"/>
<property value= "$" name= "size"/>
</cacheModel>
Mapping Class to SQL realation teable
<resultMaps>
<resultmap id= "Ygbresult" class= "Schoolentity.yg_b" >
<result property= "bid" column= "bid"/>
<result property= "bname" column= "bname"/>
<result property= "Aid" column= "aid"/>
</resultMap>
</resultMaps>
Mapping the SQl StateMent about CRUD Chenkai
<statements>
<insert id= "INSERTYGB" parameterclass= "Schoolentity.yg_b" >
INSERT into Dbo.yg_b
(Bname,
Aid
)
VALUES (#bname #, #aid #)
</insert>
<update id= "UPDATEYGB" parameterclass= "Schoolentity.yg_b" restltclass= "int" >
UPDATE Dbo.yg_b SET bname= #bname #,
Aid= #aid #
WHERE bid= #bid #
</update>
<delete id= "DELETEYGB" parameterclass= "Schoolentity.yg_b" restltclass= "int" >
DELETE from Dbo.yg_b WHERE bid= #bid #
</delete>
<select id= "SELECTALLYGB" resultclass= "Schoolentity.yg_b" >
SELECT * from Dbo.yg_b
</select>
</statements>
</sqlMap>
Note: Select Update Insert Delete must be lowercase, or an error will be

The effect is as follows: (only one single table query)

Ibatis.net Exercise