SqlServer/MyBatis SqlMapConfig datasource setup

Question

I'm working through this MyBatis tutorial, and am down to an issue with setting up the dataSource within the SqlMapConfig.xml. The tutorial is using MySql, like every other tutorial existing on the internet it seems, but I'm using SqlServer 2008.

Please help me convert the below code to connect to a local SqlServer, or help me understand how to setup environments/how the SqlMapConfig works with the connection factory.

Links to info/tutorials would be great too.

Thanks!

<?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 alias="Contact" type="com.name.model.Contact"/>
    </typeAliases>

    <environments default="development">
        <environment id="development">
          <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://localhost:3306/blog"/>
                <property name="username" value="root"/>
                <property name="password" value="root"/>
            </dataSource>
       </environment>
    </environments>

    <mappers>
       <mapper resource="com/name/data/Contact.xml"/>
    </mappers>

</configuration>

UPDATED:

<?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 alias="Contact" type="com.name.model.Contact"/>
    </typeAliases>

    <environments default="development">
        <environment id="development">
          <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="com.microsoft.sqlserver.jdbc.SQLServerDriver"/>
            <property name="url" value="jdbc:sqlserver://localhost(or name of server):1433;databaseName=yourdbname; catalogName=sameasdbname"/>
            <property name="username" value="root"/>
            <property name="password" value="root"/>
            </dataSource>
       </environment>
    </environments>

    <mappers>
       <mapper resource="com/name/data/Contact.xml"/>
    </mappers>

</configuration>

NOTE: I was using the latest JDBC driver 4 with SQL 2008, and had version issues, specifically the error: "Exception data: java.lang.UnsupportedClassVersionError (com/microsoft/sqlserver/jdbc/SQLServerDriver) bad major version at offset=6 ........."

Switch to the compatible driver sqljdbc.jar over sqljdbc3/sqljdbc4.jar to fix this.

Answer 1

To connect to SQL Server (or any other database out there) you need two basic things:

• an appropriate JDBC driver.
• appropriate properties when configuring the data saource;

For the driver I guess you can go for "the official" Microsoft JDBC Driver for SQL Server distribution which you must make available on your application's classpath, then configure the data source properties in MyBatis configuration which involves specifying the driver class (com.microsoft.sqlserver.jdbc.SQLServerDriver if I'm not mistaken) and the connection url (which must start with jdbc:sqlserver:// instead of jdbc:mysql://).

You can find more information on the official page which will provide appropriate links to help you through it all (just make sure you read the documentation for your version of SQL Server; SQL Server 2012 is out so Microsoft updated their docs).

Then depending on what types of queries you are running (for a basic tutorial as that one shouldn't be a problem) you must make sure that you use the proper SQL syntax.