mybatis-3 | MyBatis SQL mapper framework for Java | Object-Relational Mapping library

@MapperScan("com.lrs.admin.dao")
@ServletComponentScan
@SpringBootApplication
public class Application extends SpringBootServletInitializer implements EmbeddedServletContainerCustomizer{

    public static void main(String[] args) {
        SpringApplication.run(Application.class, args);
    }

@Controller
@RequestMapping("/news")
public class NewsController extends BaseController {
    private final static String qxurl="news/list";
    
    @Autowired
    private INewsService newsService;

package com.lrs.admin.service;
import java.util.List;
import java.util.Map;
import javax.servlet.http.HttpSession;
import org.springframework.stereotype.Componnt;
import com.lrs.admin.util.ParameterMap;

    @Component
    public interface INewsService {
        public List<ParameterMap> list();
        public Map<String,Object> getNews(ParameterMap pm);
        public Map<String,Object> edit(ParameterMap pm); 
        public Map<String,Object> add(ParameterMap pm,HttpSession session); 
        public Map<String,Object> del(String newsId); 
    }

public class NewsService implements INewsService {

    @Autowired
    private NewsDao newsDao;
    

public interface NewsDao {
    public List<ParameterMap> list();
    public List<ParameterMap> getAllNewsById(ParameterMap pm);
    public ParameterMap getNewsById(ParameterMap pm);
    public void updateNews(ParameterMap pm);
    public void addNews(ParameterMap pm);
    public void delNews(String roleId);
//  public void delUserRole(String roleId);
}

<?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.lrs.admin.dao.NewsDao">

</mapper>

<update id="updateHealth">
        UPDATE table d
        SET fk_table_health_status_id = 
            (SELECT pk_table_health_status_id 
            FROM stat_table_health_status sdhs
            WHERE sdhs.table_health_status = #{health})
            <if test="health == 'Failed' || health == 'Other Failed'">
            ,last_failure = NOW(),
            failure_count = failure_count + 1</if>
        WHERE d.name = #{name}
    </update>

@Stateless
public class Repository implements IRepository {

    /** The device mapper. */
    // CHECKSTYLE:OFF - SuppressWarnings - Bean is auto-generated by the iBatis mapper.
    @SuppressWarnings("cdi-ambiguous-dependency")
    @Inject
    @Mapper
    private IMapper mapper;
    
    // more queries...
    @Override
    public void updateHealth(String name, String health) {
        mapper.updateHealth(name, health);
    }
    // more queries...
}

<configuration>
    <settings>
        <setting name="autoMappingBehavior" value="PARTIAL"/>
    </settings>
    <!-- Used to configure MyBatis environment to apply your SQL Maps to the database. -->
    <environments default="development">
        <environment id="development">
            <transactionManager type="MANAGED" />
            <dataSource type="JNDI">
                <property name="data_source" value="${jdbc.url}" />
            </dataSource>
        </environment>
    </environments>
    <!-- The mappers configures the location of the xml mappers containing the CRUD functions ex against the database. -->
    <mappers>
        <package name="com/my/mapper/package"/>
    </mappers>
</configuration>

<subsystem xmlns="urn:jboss:domain:datasources:1.2">
            <datasources>
                <datasource jndi-name="java:jboss/datasources/PostgresDS" pool-name="PostgresDS" enabled="true" use-java-context="true">
                    <connection-url>jdbc:postgresql://localhost:5432/mydb</connection-url>
                    <driver>postgresql</driver>
                    <pool>
                        <min-pool-size>2</min-pool-size>
                        <max-pool-size>20</max-pool-size>
                        <prefill>true</prefill>
                    </pool>
                    <security>
                        <user-name>user</user-name>
                        <password>super_secret_password</password>
                    </security>
                    <validation>
                        <check-valid-connection-sql>SELECT 1</check-valid-connection-sql>
                        <validate-on-match>false</validate-on-match>
                        <background-validation>false</background-validation>
                        <use-fast-fail>false</use-fast-fail>
                    </validation>
                </datasource>

<subsystem xmlns="urn:jboss:domain:transactions:1.5">
            <core-environment>
                <process-id>
                    <uuid/>
                </process-id>
            </core-environment>
            <recovery-environment socket-binding="txn-recovery-environment" status-socket-binding="txn-status-manager"/>
            <coordinator-environment default-timeout="1800"/>
        </subsystem>

2021-10-27 13:07:15 EDT LOG:  process 15612 still waiting for ShareLock on transaction 10150 after 1005.830 ms
2021-10-27 13:07:15 EDT DETAIL:  Process holding the lock: 13404. Wait queue: 15612.
2021-10-27 13:07:15 EDT CONTEXT:  while updating tuple (1,24) in relation "dyn_device"
2021-10-27 13:07:15 EDT STATEMENT:  UPDATE dyn d
            SET fk_health_status_id = 
                (SELECT pk_health_status_id 
                FROM stat_health_status sdhs
                WHERE sdhs.health_status = $1)
                 
            WHERE d.name = $2

@RestController
@RequestMapping("/api/myAPI")
public class MyController{
    @Resource
    private MyService service;

    @GetMapping(value = "myString")
    public String getBillingCompany() {
        return this.service.getDAO().getMyString();
    }
}

@Repository
@Mapper
public interface MyDAO{
    String getMyString();
}

<mapper namespace="com.package.MyDAO">
    <select id="getMyString" resultType="String">
        SELECT 'My desired result' FROM A_TABLE
    </select>
    ...
</mapper>

HttpErrorResponse: {
    "headers": {
        "normalizedNames": {},
        "lazyUpdate": null
    },
    "status": 200,
    "statusText": "OK",
    "url": "http://localhost:4200/api/myAPI/myString",
    "ok": false,
    "name": "HttpErrorResponse",
    "message": "Http failure during parsing for http://localhost:4200/api/myAPI/myString",
    "error": {
        "error": {  SyntaxError: Unexpected number in JSON at position 2
                    at JSON.parse (<anonymous>)
                    at XMLHttpRequest.onLoad (http://localhost:4200/vendor.js:18158:51)
                    at ZoneDelegate.push../node_modules/zone.js/dist/zone.js.ZoneDelegate.invokeTask (http://localhost:4200/polyfills.js:21266:35)
                    at Object.onInvokeTask (http://localhost:4200/vendor.js:74037:33)
                    at ZoneDelegate.push../node_modules/zone.js/dist/zone.js.ZoneDelegate.invokeTask (http://localhost:4200/polyfills.js:21265:40)
                    at Zone.push../node_modules/zone.js/dist/zone.js.Zone.runTask (http://localhost:4200/polyfills.js:21033:51)
                    at ZoneTask.push../node_modules/zone.js/dist/zone.js.ZoneTask.invokeTask [as invoke] (http://localhost:4200/polyfills.js:21348:38)
                    at invokeTask (http://localhost:4200/polyfills.js:22516:18)
                    at XMLHttpRequest.globalZoneAwareCallback (http://localhost:4200/polyfills.js:22553:25)
                 },
        "text": "My desired result"
    }
}

<?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>
    <typeAliases>
        <typeAlias type="java.lang.String" alias="String"/>
    </typeAliases>
</configuration>

@RestController
@RequestMapping("/api/myAPI")
public class MyController{
    @Resource
    private MyService service;

    @GetMapping(value = "myString")
    public Result<String> getBillingCompany() {
        return new Result<>(this.service.getDAO().getMyString());
    }
}

public class Result<T> extends ServiceResult {
    public Result(T data) {
        this.setData(data);
    }
}

public class ServiceResult {
    private Object data;

    ...

    public void setData(Object data) {
        this.data = data;
    }

    public Object getData() {
        return this.data;
    }
}

org.springframework.dao.DataIntegrityViolationException:
### Error updating database. Cause: java.sql.SQLIntegrityConstraintViolationException: Column 'title' cannot be null

package sample.spring.yse;

import java.util.Map;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.servlet.ModelAndView;

@Controller
public class BookController {
    @Autowired
    BookService bookService;

    @RequestMapping(value = "/create", method= RequestMethod.POST)
    public ModelAndView createPost(@RequestParam Map<String, Object> map) {
        ModelAndView mav = new ModelAndView();

        String bookId = this.bookService.create(map);
        if (bookId == null) {
            mav.setViewName("redirect:/create");
        }else {
            mav.setViewName("redirect:/detail?bookId=" + bookId); 
        }  

        return mav;
    }
}

<!DOCTYPE html>
<html>
<head>
<meta charset="EUC-KR">
<title>책 생성하기</title>
</head>
<body>
<h1>create book</h1>
<form method="post">
<p>제목 : <input type="text" name="title"></p>
<p>카테고리 : <input type="text" name="category"></p>
<p>가격 : <input type="text" name="price"></p>
<p><input type="submit" value="save"></p>
 <input type="hidden" name="${_csrf.parameterName}" value="${_csrf.token}" />    
</form>
</body>
</html>

<?xml version="1.0" encoding="UTF-8"?>
<beans:beans xmlns="http://www.springframework.org/schema/mvc"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xmlns:beans="http://www.springframework.org/schema/beans"
    xmlns:context="http://www.springframework.org/schema/context"
    xsi:schemaLocation="http://www.springframework.org/schema/mvc https://www.springframework.org/schema/mvc/spring-mvc.xsd
        http://www.springframework.org/schema/beans https://www.springframework.org/schema/beans/spring-beans.xsd
        http://www.springframework.org/schema/context https://www.springframework.org/schema/context/spring-context.xsd">

    <!-- DispatcherServlet Context: defines this servlet's request-processing infrastructure -->
    
    <!-- Enables the Spring MVC @Controller programming model -->
    <annotation-driven />

    <!-- Handles HTTP GET requests for /resources/** by efficiently serving up static resources in the ${webappRoot}/resources directory -->
    <resources mapping="/resources/**" location="/resources/" />

    <!-- Resolves views selected for rendering by @Controllers to .jsp resources in the /WEB-INF/views directory -->
    <beans:bean class="org.springframework.web.servlet.view.InternalResourceViewResolver">
        <beans:property name="prefix" value="/WEB-INF/views" />
        <beans:property name="suffix" value=".jsp" />
    </beans:bean>
    
    <context:component-scan base-package="sample.spring.yse" />     
</beans:beans>

<?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="book">
<insert id="insert" parameterType="hashMap" useGeneratedKeys="true" keyProperty="book_id">  
    <![CDATA[
    insert into book
    (title, category, price) 
    values
    (#{title}, #{category}, #{price})
    ]]>
</insert>
</mapper>

package sample.spring.yse;

import java.util.Map;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

@Service
public class BookServiceImpl implements BookService {
 @Autowired
 BookDao bookDao;
 @Override
 public String create(Map<String, Object> map) {
     int affectRowCount = this.bookDao.insert(map);
     if (affectRowCount ==  1) {
         return map.get("book_id").toString();
     }
     return null;

 }
}

package sample.spring.yse;

import java.util.Map;

public interface BookService {
    String create(Map<String, Object> map);
}

public class A {
    int id;
    String name;
    B b;
}

public class B {
    long number;
    String description;
}

@Results( value = { ..., @Result(property = "prop", javaType = B.class, one = @One(resultMap = "de.mycompany.path.to.ClassADao.resultMapId")) })

Caused by: java.lang.IllegalStateException: No typehandler found for property prop

create or replace PROCEDURE TEST_PROC
(
 PARAM_1 IN NUMBER 
, PARAM_2 IN NUMBER 
) AS 
BEGIN
    INSERT INTO sometable(VALUE1, VALUE2) VALUES (PARAM_1, PARAM_2);
END TEST_PROC;

<insert id="insertData" parameterType="model" statementType="CALLABLE">
        {call TEST_PROC(
            #{param_1, jdbcType=INTEGER,mode=IN},
            #{param_2, jdbcType=INTEGER,mode=IN}})}
    </insert>

<select id="selectBlog" resultSets="blogs,authors" resultMap="blogResult" statementType="CALLABLE">
  {call getBlogsAndAuthors(#{id,jdbcType=INTEGER,mode=IN})}
</select>

<?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.testing.db1.DB1GetStudentDataMapper">

<select id="selectAllStudents" resultType="java.util.Map">
        SELECT STUDENT_CD, STUDENT_NM, PARENT_CD, CREATED_DATE
        FROM STUDENT
        WHERE STD_STATUS='ACT'
</select>

</mapper>

<?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.testing.db2.DB2InsertStudentMapper">
                
<insert id="insertStudent" parameterType="java.util.HashMap">
  INSERT INTO STUDENT
  <!-- dynamically select column names from hashmap -->
  (#{stdMap.keySet}) // this is not working - its coming as null
   <!-- dynamically select values for the above columns from hashmap -->
   VALUES (#{stdMap.values}) // this is not working - its coming as null
</insert>
           
</mapper>

public interface TMODSBDataRefreshMapper {
    
    void insertStudent(@Param("stdMap") HashMap stdMap);
}

public class Student {
    
    // I have defaultExecutorType as BATCH in my mapper config file
    
        private DB1GetStudentDataMapper db1Mapper; // Interface Mapper for first data source
        private DB2InsertStudentMapper db2Mapper; // Interface Mapper for second data source
        
        public processStudent() throws Exception {
            
            List<HashMap> rs = db1Mapper.selectAllStudents(); // Gets some 15k+ records
            for(int i =0; i < rs.size(); i++) { // so this will loop through 15k+ records
                HashMap result = rs.get(i);
                System.out.println(result.keySet()); // prints column names from select query [STUDENT_CD, STUDENT_NM, PARENT_CD, CREATED_DATE]
                System.out.println(result.values()); // prints above column values of first data set [1001, Mike, 5001, 2021-07-01]
                
                // All I am trying is to insert above 15k records into different database dynamically rather than creating POJO
                db2Mapper.insertStudent(result);
            }
            
        }
            
}

@Service
public class YahooBudgetUpdateService {
    @Autowired
    private AccounttUpdateMapper mapper; 
    
    public void accountUpdate() throws Exception {
       List<String> ids = new ArrayList<String>();
       ids.add("1"); 
       ids.add("2"); 
       ids.add("3"); 
       updateAcnt(ids);
    }

    @Transactional 
    public void updateAcnt(List<String> ids) throw Exception {
        for(int i=0; i<ids.size; i++){
            updateById(ids.get(i));  
        }    
    }
}

public interface AccountUpdateMapper {
     int updateById(String id);
}

<?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="src.mapper.AccountUpdateMapper">
<update id="updateById">
    UPDATE ad_cpnt
    SET
        updatedOn = now(),
        lst_updtd_dt= now(),
    where
        id = #{id}
</update>
</mapper>

    ### Error building SqlSession.
### The error may exist in mybatis/Student.xml
### The error occurred while processing mapper_resultMap[result]
### Cause: org.apache.ibatis.builder.BuilderException: Error parsing SQL Mapper Configuration. Cause: org.apache.ibatis.builder.BuilderException: Error parsing Mapper XML. Cause: org.apache.ibatis.builder.BuilderException: Error resolving class. Cause: org.apache.ibatis.type.TypeException: Could not resolve type alias 'java.com.model.Siswa'.  Cause: java.lang.ClassNotFoundException: Cannot find class: java.com.model.Siswa
    at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:30)
    at org.apache.ibatis.session.SqlSessionFactoryBuilder.build(SqlSessionFactoryBuilder.java:54)
    at org.apache.ibatis.session.SqlSessionFactoryBuilder.build(SqlSessionFactoryBuilder.java:38)
    at com.repository.SiswaRepo.saveSiswa(SiswaRepo.java:51)
    at com.repository.SiswaRepo$$FastClassBySpringCGLIB$$550dcd02.invoke(<generated>)
    at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218)
    at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:771)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
    at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:749)
    at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:139)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
    at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:749)
    at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:691)
    at com.repository.SiswaRepo$$EnhancerBySpringCGLIB$$98743102.saveSiswa(<generated>)
    at com.controller.SiswaController.inputSiswa(SiswaController.java:39)
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.base/java.lang.reflect.Method.invoke(Method.java:566)

    <?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 = "Siswa">
    <insert id = "insert" parameterType = "java.com.model.Siswa">
        INSERT INTO siswas (nama, alamat, status) VALUES (#{fullname}, #{address},
        #{status});
        INSERT INTO nilai (biologi, physics, calculus) VALUES (#{biologi}, #{fisika},
        #{kalkulus});
        <selectKey keyProperty = "id" resultType = "int" order = "AFTER">
            select last_insert_id() as id
        </selectKey>
    </insert>

@RequestMapping(value = "/input", method = RequestMethod.POST)
public ResponseEntity<?> inputSiswa(@RequestBody JSONObject jobj) throws ParseException, IIOException {
    Adapter adapter = new Adapter();
    ArrayList<Siswa> array = new ArrayList<>();
    array = adapter.buatSiswadariJSON(jobj);
    for (int i = 0; i <array.size();i++) {
        siswaRepository.saveSiswa(array.get(i));
    }
    return (new ResponseEntity<>("data has been inputed", HttpStatus.CREATED));
}

@Override
    public void saveSiswa(Siswa siswa) throws IIOException {
        try {
            Reader reader = Resources.getResourceAsReader("SqlMapConfig.xml");
            SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);
            SqlSession session = sqlSessionFactory.openSession();

            //select contact all contacts
        String nama = siswa.getFullname();
        String address = siswa.getAddress();
        String status = siswa.getStatus();
        float biologi = siswa.getBiologi();
        float fisika = siswa.getFisika();
        float kalkulus = siswa.getKalkulus();

        Siswa siswa1 = new Siswa(nama,address,status );
        session.insert("Student.insert", siswa1);

        Siswa siswa2 = new Siswa(biologi,fisika,kalkulus);
        session.insert("Student.insert", siswa2);

        System.out.println("Records Read Successfully ");
        session.commit();
        session.close();
    }catch (Exception e){
        e.printStackTrace();
    }
}

@Data
public class BaseUser {

    protected Integer id;
    protected String username;
    protected String password;
    protected Date birthday;
    protected String email;
    protected String phone;
    protected String address;
    protected Boolean status;
    protected String creator;
    protected Date createTime;
    protected Date updateTime;
    protected Set<GrantedAuthority> authorities;

  <select id="selectByAdminId" resultMap="AdminResultMap">
        SELECT a.id, a.password, a.admin_name, a.birthday, a.email, a.phone, a.address, a.status , au.authority as authority
        FROM admin a
        JOIN user_role ur ON ur.user_id = a.id
        JOIN role r ON r.id = ur.role_id
        JOIN role_authority ra ON ra.role_id = r.id
        JOIN authority au ON au.id = ra.authority_id
        WHERE a.id = #{id} and r.status = 1 AND au.status = 1
    </select>

 <resultMap id="BaseUserResultMap" type="com.example.abstractionizer.student.management.system5.db.rmdb.entities.BaseUser">
        <id column="id" property="id" jdbcType="INTEGER"/>
        <result column="password" property="password" jdbcType="VARCHAR"/>
        <result column="admin_name" property="username" jdbcType="VARCHAR"/>
        <result column="birthday" property="birthday" jdbcType="DATE"/>
        <result column="email" property="email" jdbcType="VARCHAR"/>
        <result column="phone" property="phone" jdbcType="VARCHAR"/>
        <result column="address" property="address" jdbcType="VARCHAR"/>
        <result column="status" property="status" jdbcType="TINYINT"/>
        <collection property="authorities" ofType="org.springframework.security.core.GrantedAuthority">
            <result column="authority" jdbcType="VARCHAR" typeHandler="com.example.abstractionizer.student.management.system5.handlers.GrantedAuthorityTypeHandler"/>
        </collection>
    </resultMap>

    <resultMap id="AdminResultMap" type="com.example.abstractionizer.student.management.system5.db.rmdb.entities.Admin" extends="BaseUserResultMap">

    </resultMap>

@Slf4j
@MappedJdbcTypes({JdbcType.VARCHAR})
public class GrantedAuthorityTypeHandler extends BaseTypeHandler<GrantedAuthority> {

    @Override
    public void setNonNullParameter(PreparedStatement preparedStatement, int i, GrantedAuthority grantedAuthority, JdbcType jdbcType) throws SQLException {
        preparedStatement.setObject(i, grantedAuthority.toString(), jdbcType.TYPE_CODE);
    }

    @Override
    public GrantedAuthority getNullableResult(ResultSet resultSet, String s) throws SQLException {
        return new SimpleGrantedAuthority(resultSet.getString(s));
    }

    @Override
    public GrantedAuthority getNullableResult(ResultSet resultSet, int i) throws SQLException {
        return new SimpleGrantedAuthority(resultSet.getString(i));
    }

    @Override
    public GrantedAuthority getNullableResult(CallableStatement callableStatement, int i) throws SQLException {
        return new SimpleGrantedAuthority(callableStatement.getNString(i));
    }
}