使用Spring连接MySql数据库

一、首先安装Mysql数据库，之后，创建数据库及表：

1. 安装之后，输入mysql -u root -p，之后再输入对应的密码，打开mysql的控制台；
2. 创建数据库mydb，create database mydb;
3. 切换到mydb数据库下，use mydb；

4. 创建数据表user， create table user (id int auto_increment, name varchar(20), primary key(id));

5. 使用show tables查看是否创建成功。

二、创建Maven项目：

1. New Project，选择Maven，一路next即可创建；
2. 在pom.xml中加入需要的jar包：

   <dependencies>    <dependency>        <groupId>mysql</groupId>        <artifactId>mysql-connector-java</artifactId>        <version>5.1.38</version>    </dependency>    <dependency>        <groupId>commons-pool</groupId>        <artifactId>commons-pool</artifactId>        <version>1.6</version>    </dependency>    <dependency>        <groupId>commons-dbcp</groupId>        <artifactId>commons-dbcp</artifactId>        <version>1.4</version>    </dependency>    <dependency>        <groupId>org.springframework</groupId>        <artifactId>spring-context</artifactId>        <version>4.2.6.RELEASE</version>    </dependency>    <dependency>        <groupId>org.springframework</groupId>        <artifactId>spring-beans</artifactId>        <version>4.2.6.RELEASE</version>    </dependency>    <dependency>        <groupId>org.springframework</groupId>        <artifactId>spring-core</artifactId>        <version>4.2.6.RELEASE</version>    </dependency>    <dependency>        <groupId>junit</groupId>        <artifactId>junit</artifactId>        <version>4.12</version>        <scope>test</scope>    </dependency></dependencies>

3. 在resources文件夹下创建一个beans.xml文件（这个文件夹的是资源文件）；

4. 打开Spring的说明文档，点击打开链接，找到The IoC container一节中的Container Overview小节，点开Configuration metadata，复制一份，拷贝到beans.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"       xsi:schemaLocation="http://www.springframework.org/schema/beans           http://www.springframework.org/schema/beans/spring-beans.xsd">  <bean id="..." class="...">    <!-- collaborators and configuration for this bean go here -->  </bean>  <bean id="..." class="...">    <!-- collaborators and configuration for this bean go here -->  </bean>  <!-- more bean definitions go here --></beans>

5. 打开Spring的说明文档，点击打开链接，还是在The IoC container一节中的Container Overview小节下，找到Annotation-based container configuration小节，复制其xml头，添加以下绿色部分，添加context标签：

   <?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"     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:annotation-config/></beans>

6. 找到Spring说明文档的The IoC container一节中的Container Overview下，Dependencies小节，点击打开Dependencies and configuration in detail，可以看到mysql的配置方式，将其拷贝到beans.xml中：

   <bean id="myDataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close"><!-- results in a setDriverClassName(String) call --><property name="driverClassName" value="com.mysql.jdbc.Driver"/><property name="url" value="jdbc:mysql://localhost:3306/mydb"/><property name="username" value="root"/><property name="password" value="masterkaoli"/></bean>

7. 在src/main/java文件夹下创建today包，在today包下创建dao、impl和model文件夹，在dao文件夹下创建接口UserDAO：

   package today.dao;import today.model.User;public interface UserDAO {    public void save(User user);}

   
   
8. 在impl创建类UserDaoImpl：

   package today.impl;import org.springframework.stereotype.Component;import today.dao.UserDAO;import today.model.User;import javax.annotation.Resource;import javax.sql.DataSource;import java.sql.Connection;import java.sql.SQLException;@Componentpublic class UserDaoImpl implements UserDAO {    private DataSource dataSource;    //使用接口save来保存user的数据    public void save(User user) {        Connection connection = null;        try {            connection = dataSource.getConnection();            connection.createStatement().executeUpdate(                    "insert into user values("+ user.getId()                            +",'"+ user.getUsername() +"')");        } catch (SQLException e) {            e.printStackTrace();        } finally {            try {                connection.close();            } catch (SQLException e) {                e.printStackTrace();            }        }    }    public DataSource getDataSource() {        return dataSource;    }    //使用setter注入myDataSource    @Resource(name = "myDataSource")    public void setDataSource(DataSource dataSource) {        this.dataSource = dataSource;    }}

   
   
9. 在model创建类User：

   package today.model;public class User{    private int id;    private String username;    public User(int id, String username) {        this.id = id;        this.username = username;    }    public int getId() {        return id;    }    public void setId(int id) {        this.id = id;    }    public String getUsername() {        return username;    }    public void setUsername(String username) {        this.username = username;    }}

10. 运行结果