使用Spring和Maven连接MySQL数据库

Mysql版本：8.0.17

 

1.在pom.xml中加入以下依赖：

<!-- spring的jdbc https://mvnrepository.com/artifact/org.springframework/spring-jdbc -->
<dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-jdbc</artifactId>
    <version>5.2.4.RELEASE</version>
</dependency>

	
<!-- mysql的connector https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.15</version>
</dependency>

2.配置Beans.xml，在beans中添加以下的bean

<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">

    <!-- 不同于以往的com.mysql.jdbc.Driver，现在改成了com.mysql.cj.jdbc.Driver -->
    <property name="driverClassName" value="com.mysql.cj.jdbc.Driver">

    <!-- 记得更改数据库端口和名称 -->
    <property 
        name="url" 
        value="jdbc:mysql://localhost:3306/spring?serverTimezone=GMT%2B8"/>
    
    <!-- 用户名和密码 -->
    <property name="username" value="root"/>
    <property name="password" value="passowrd"/>
</bean>

如果正常的话，应该就已经连接成功了。

 

再放个简单的操作实例吧----------------------------------------------------------------

Beans.xml的配置：

<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
    <property name="driverClassName" value="com.mysql.cj.jdbc.Driver"></property>
    <property name="url" value="jdbc:mysql://localhost:3306/spring?serverTimezone=GMT%2B8"/>
    <property name="username" value="root"/>
    <property name="password" value="root"/>
</bean>
	
<bean id="testDao" class="com.shitspring.fuckspring.TestDao" >
    <property name="dataSource" ref="dataSource"/>
</bean>

 

用于测试的表的结构：

CREATE TABLE `test`  (
  `id` int(11) NOT NULL,
  `name` varchar(255) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) 
);

用于储存的bean：

public class TestUnit {
	private int id;
	private String name;
	
	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	
	@Override
	public String toString() {
		return id+name;
	}
}

 

定义DAO接口，指定DAO层都可以干些什么

public interface ITestDao {
	
    //设置数据源
	public void setDataSource(DataSource dataSource);
	
    //添加
	public void add(int unitId,String name);
	
    //删除
	public void delete(int unitId);
	
    //修改
	public void update(int unitId,String name);
	
    //获取某一个单元
	public TestUnit getUnit(int unitId);
	
    //获取所有的单元
	public List<TestUnit> getAllUnits();
}

实现接口

public class TestDao implements ITestDao {

	private JdbcTemplate jdbcTemplate;
	
	@Override
	public void setDataSource(DataSource dataSource) {
		jdbcTemplate = new JdbcTemplate(dataSource);
	}

	@Override
	public void add(int unitId, String name) {
		String sql = "insert into test values(?,?)";
		jdbcTemplate.update(sql,unitId,name);
		System.out.println("add");
	}

	@Override
	public void delete(int unitId) {
		String sql = "delete from test where id=?";
		jdbcTemplate.update(sql,unitId);
		System.out.println("delete");
	}

	@Override
	public void update(int unitId, String name) {
		String sql = "update test set name=? where id=?";
		jdbcTemplate.update(sql,name,unitId);
		System.out.println("update");
	}
	
	private RowMapper<TestUnit> mapper = (ResultSet rs, int rowNum)-> {
		TestUnit result = new TestUnit();
		result.setId(rs.getInt("id"));
		result.setName(rs.getString("name"));
		return result;
	};

	@Override
	public TestUnit getUnit(int unitId) {
		String sql = "select * from test where id=?";
		return jdbcTemplate.queryForObject(sql, mapper ,unitId);
	}

	@Override
	public List<TestUnit> getAllUnits() {
		String sql = "select * from test";
		return jdbcTemplate.query(sql, mapper);
	}
}

接下来就是调用了

public class App {
	
    public static void main( String[] args )throws Exception{
    	
        ConfigurableApplicationContext context = new FileSystemXmlApplicationContext("src\\Beans.xml");
        
        TestDao testDao = (TestDao)context.getBean("testDao");
        testDao.add(0, "张三");
        testDao.add(1, "李四");
        testDao.add(2, "王五");
        
        System.out.println(testDao.getUnit(1).getName());
        
        testDao.update(1, "马六");
        testDao.delete(0);
        
        System.out.println(testDao.getAllUnits());

        context.close();
    }
}

执行结果：

add
add
add
李四
update
delete
[1马六, 2王五]