MyBatis创建存储过程的实例代码_动力节点Java学院整理
所需要用到的其他工具或技术:
项目管理工具 : maven
测试运行工具 : junit
数据库 : derby
本节需要用到的有2部分,第一部分是如何在derby中创建存储过程,第二部分是如何在mybatis中调用存储过程
一. 在derby中创建存储过程
在eclipse中创建一个新的普通java项目命名为test_store_procedure
在com.bjpowernode.practice包下创建一个class命名为storeprocedureoperationclass.class
package com.bjpowernode.practice; import java.sql.connection; import java.sql.drivermanager; import java.sql.preparedstatement; import java.sql.sqlexception; /** * * 存储过程类 * * @author freud * */ public class storeprocedureoperationclass { /** * * 执行插入的存储过程 * * @param id * @param username * @param password * @param nickname * @throws sqlexception */ public static void insertdata(int id, string username, string password, string nickname) throws sqlexception { connection connection = drivermanager.getconnection("jdbc:default:connection"); preparedstatement p = connection.preparestatement("insert into user_test_tb(id,username,password,nickname) values(?,?,?,?)"); p.setint(1, id); p.setstring(2, username); p.setstring(3, password); p.setstring(4, nickname); system.out.println("insert values (id=" + id + ",username=" + username + ",password=" + password + ",nickname=" + nickname + ")"); p.executeupdate(); p.close(); connection.close(); } }
3. 利用jar命令或者eclipse工具导出到c:\freud\test_store_procedure.jar
4. 在ij命令行中声明存储过程
create procedure freud.insert_user(in the_id integer, in the_username varchar(20), in the_password varchar(20), in the_nickname varchar(20)) parameter style java modifies sql data language java external name 'com.bjpowernode.practice.storeprocedureoperationclass.insertdata'; 在 ij 控制台中调用call sqlj.install_jar('c:\freud\test_store_procedure.jar', 'freud.test_sprocedure', 0);将 jar 包导入到 freud模式中,并命名为test_sprocedure。
6. 在 ij 控制台中调用call syscs_util.syscs_set_database_property('derby.database.classpath', 'freud.test_sprocedure');
将 jar 包加入到数据库 classpath 搜索路径中。
这样,derby的存储过程就算创建完成了
二.在mybatis中调用存储过程
maven dependencies: <dependencies> <dependency> <groupid>org.mybatis</groupid> <artifactid>mybatis</artifactid> <version>3.2.7</version> </dependency> <dependency> <groupid>junit</groupid> <artifactid>junit</artifactid> <version>4.9</version> <scope>test</scope> </dependency> <dependency> <groupid>org.apache.derby</groupid> <artifactid>derby</artifactid> <version>10.10.2.0</version> </dependency> <dependency> <groupid>org.apache.derby</groupid> <artifactid>derbyclient</artifactid> <version>10.10.2.0</version> </dependency> </dependencies>
mybatis配置文件 src/main/resource源目录下
test-mybatis-configuration.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> <property name="driver" value="org.apache.derby.jdbc.clientdriver" /> <property name="url" value="jdbc:derby://localhost:1527/freud;create=true" /> </properties> <environments default="development"> <environment id="development"> <transactionmanager type="jdbc" /> <datasource type="pooled"> <property name="driver" value="${driver}" /> <property name="url" value="${url}" /> </datasource> </environment> </environments> <mappers> <mapper resource="com/freud/practice/usermapper.xml" /> </mappers> </configuration>
user.java对象类(src/main/java/com/freud/practice目录下)
package com.bjpowernode.practice; public class user { private integer id; private string username; private string password; private string nickname; public integer getid() { return id; } public void setid(integer id) { this.id = id; } public string getusername() { return username; } public void setusername(string username) { this.username = username; } public string getpassword() { return password; } public void setpassword(string password) { this.password = password; } public string getnickname() { return nickname; } public void setnickname(string nickname) { this.nickname = nickname; } }
usermapper.xml mapper文件(src/main/java/com.bjpowernode.practice目录下)
<?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 namespace="com.bjpowernode.practice.usermapper"> <!-- 查询 --> <select id="getuser" resulttype="com.bjpowernode.practice.user"> select * from user_test_tb </select> <!-- 调用插入的存储过程 --> <insert id="insertuser" statementtype="callable"> call freud.insert_user(#{id},#{username},#{password},#{nickname}) </insert> </mapper>
usermapper.java mapper类(src/main/java/com.bjpowernode.practice目录下)
package com.bjpowernode.practice; import java.util.list; public interface usermapper { public list<user> getuser(); public void insertuser(user user); }
测试类testmybatis.java(src/test/java/com.bjpowernode.practice目录下)
package com.bjpowernode.practice; import java.io.inputstream; import java.text.messageformat; import java.util.list; import org.apache.ibatis.session.sqlsession; import org.apache.ibatis.session.sqlsessionfactory; import org.apache.ibatis.session.sqlsessionfactorybuilder; import org.junit.before; import org.junit.test; public class testmybatis { private string source; private inputstream inputstream; private sqlsessionfactory sqlsessionfactory; @before public void setup() { /** * 准备mybatis运行环境 */ source = "test-mybatis-configuration.xml"; inputstream = testmybatis.class.getclassloader().getresourceasstream(source); sqlsessionfactory = new sqlsessionfactorybuilder().build(inputstream); } @test public void testinsert() { try { // 获取session连接 sqlsession session = sqlsessionfactory.opensession(); // 获取mapper usermapper usermapper = session.getmapper(usermapper.class); system.out.println("test insert start..."); // 显示插入之前user信息 system.out.println("before insert"); this.printusers(usermapper.getuser()); // 执行插入 usermapper.insertuser(this.mockuser("freu_ins_user", "freud_ins_pass", "freud_ins_nick")); // 提交事务 session.commit(); // 显示插入之后user信息 system.out.println("\r\nafter insert"); this.printusers(usermapper.getuser()); system.out.println("test insert finished..."); } catch (exception e) { e.printstacktrace(); } } /** * * 组装一个user对象 * * @return */ public user mockuser(string username, string password, string nickname) { user user = new user(); user.setid(50); user.setusername(username); user.setpassword(password); user.setnickname(nickname); return user; } /** * * 打印用户信息到控制台 * * @param users */ private void printusers(final list<user> users) { int count = 0; for (user user : users) { system.out.println(messageformat.format("==user[{0}]=================", ++count)); system.out.println("user id: " + user.getid()); system.out.println("user username: " + user.getusername()); system.out.println("user password: " + user.getpassword()); system.out.println("user nickname: " + user.getnickname()); } } }
总结
以上所述是小编给大家介绍的mybatis创建存储过程的实例代码_动力节点java学院整理,希望对大家有所帮助