Spring database connection in IDEA

I'm learning Spring through an online course these days. But the teacher uses MyEclipse while I'm using IDEA, so I conclude the differences and write this page to remind me how to make the database connection in IDEA.

1. Build  project

Follow these steps:

2. Import JARs

We need to import some jars, they are: (ignore the version)

c3p0-0.9.5.2.jar
mchange-commons-java-0.2.9.jar
mysql-connector-java-6.0.6.jar

You can download them at here: https://repo.spring.io/webapp/#/home

3. Connect with your database

Choose a database you have in your computer, mine is MySQL.

Input the name of your database in blank "Database" and fill in "User" and "Password" to let your programme access your database.

We can see our data here.

4. Hibernate & Persistence configuration

Hibernate:

Choose a package and check your Database Schema:

Persistence:

5. Database.properties

We need to create a file whose name ends with ".properties" to record some configuration infomation about database.

Here is the content written in this file : 

jdbc.driver=com.mysql.cj.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/spring_transaction
jdbc.username=root
jdbc.password=root

"jdbc.driver" is just a name, you can also write"driver".

Remember this file! Because it might occur a bug later. If so, we need to revise the content later.

If you don't know how to fill it, you can check your "hibernate.cfg.xml" or "persistence.xml":

6. Test demo

Here are codes:

package demo1;

/**
 * 转账DAO层接口
 */
public interface AccountDAO {

    /**
     * @param outAccount 转出账户
     * @param money 金额
     */
    public void outMoney(String outAccount, double money);


    /**
     * @param inAccount 转入账户
     * @param money 金额
     */
    public void inMoney(String inAccount, double money);

}

package demo1;

import org.springframework.jdbc.core.support.JdbcDaoSupport;

public class AccountDAOImpl extends JdbcDaoSupport implements AccountDAO {
    //JdbcDaoSupport是数据库的一个支持类，提供基本操作

    @Override
    public void outMoney(String outAccount, double money) {
        String sql = "update account set money = money - ? where name = ?";
        this.getJdbcTemplate().update(sql, money, outAccount);
    }

    @Override
    public void inMoney(String inAccount, double money) {
        String sql = "update account set money = money + ? where name = ?";
        this.getJdbcTemplate().update(sql, money, inAccount);
    }
}

package demo1;

/**
 * 转账案例业务层接口
 *
 */
public interface AccountService {
    /**
     * @param outAccount 转出账户
     * @param inAccount 转入账户
     * @param money 金额
     */
    public void transfer(String outAccount, String inAccount, double money);
}

package demo1;

/**
 * 转账实现类
 */
public class AccountServiceImpl implements AccountService {
    private AccountDAO accountDAO;

    public void setAccountDAO(AccountDAO accountDAO) {
        this.accountDAO = accountDAO;
    }

    @Override
    public void transfer(String outAccount, String inAccount, double money) {
        accountDAO.outMoney(outAccount, money);
        accountDAO.inMoney(inAccount, money);
    }
}

package demo1;

import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;


public class demoTest {

    public static void main(String[] args) {
        ApplicationContext context = new ClassPathXmlApplicationContext("spring-config.xml");
        AccountService myService = (AccountService) context.getBean("accountService");
        myService.transfer("bbb","aaa",200);
        System.out.println("finish");
    }
}

Then, we need to configure "spring-config.xml":

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

    <context:component-scan base-package="database"></context:component-scan>

    <!--引入外部属性文件-->
    <context:property-placeholder location="database.properties"></context:property-placeholder>

    <!--配置c3p0连接池-->
    <!--注意property name是c3p0里已经设定好了的，要对应-->
    <bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
        <property name="driverClass" value="${jdbc.driver}"></property>
        <property name="jdbcUrl" value="${jdbc.url}"></property>
        <property name="user" value="${jdbc.username}"></property>
        <property name="password" value="${jdbc.password}"></property>
    </bean>

    <!--配置业务层-->
    <bean id="accountService" class="demo1.AccountServiceImpl">
        <property name="AccountDAO" ref="accountDAO"></property>
    </bean>

    <!--配置DAO层-->
    <bean id="accountDAO" class="demo1.AccountDAOImpl">
        <property name="dataSource" ref="dataSource"></property>
    </bean>

</beans>

7. Run demo

Run the demo and see what will happen

警告: com.mchange.v2.resourcepool.BasicResourcePool$ScatteredAcquireTask@324b6d81 -- Acquisition Attempt Failed!!! Clearing pending acquires. While trying to acquire a needed new resource, we failed to succeed more than the maximum number of allowed acquisition attempts (30). Last acquisition attempt exception: 
java.sql.SQLException: The server time zone value '�й���׼ʱ��' is unrecognized or represents more than one time zone. You must configure either the server or JDBC driver (via the serverTimezone configuration property) to use a more specifc time zone value if you want to utilize time zone support.

Here is a warning. Don't worry, it is not your fault, seems like a bug in mysql-connector-java.jar.

Check here to see details: https://confluence.atlassian.com/bitbucketserverkb/database-migration-to-mysql-fails-java-sql-sqlexception-the-server-time-zone-value-cdt-is-unrecognized-or-represents-more-than-one-time-zone-847457613.html

To fix it, we need to return to "database.properties" and revise the content

jdbc.driver=com.mysql.cj.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/spring_transaction?characterEncoding=utf8&useUnicode=true&sessionVariables=storage_engine%3DInnoDB&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC
jdbc.username=root
jdbc.password=root

8. See result

Run the project again and we can see the data has changed.