Mac Eclipse spring + maven 连接数据库

1、首先看下项目目录

2、新建一个Spring Start Project，可参考上篇文章

3、我们利用之前建的数据库的表Booklist和模型BookListModel

4、新建一个spring bean configuration file的配置文件 applicationContext.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 = "dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
         <property name="driverClassName"  value="com.mysql.jdbc.Driver" />
         <property name="url" value="jdbc:mysql://localhost:3306/new_schema"/>
            <property name="username" value="root"/>  
            <property name="password" value="123456" />  
    </bean>

</beans>

5、在ConnectDataBaseApplication写相关sql查询代码

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

        content = new ClassPathXmlApplicationContext("applicationContext.xml");
        DataSource dataSource = content.getBean("dataSource", DataSource.class);
        String sql = "select * from Booklist";

        Connection connection;

          List<BookListModel> list = new ArrayList<BookListModel>();//list对象  
        try {
            connection = dataSource.getConnection();
            Statement stm = connection.createStatement(); 
            ResultSet ret = stm.executeQuery(sql);
            while (ret.next()) {
                //注意数据类型必须与数据库的一致
                int id = ret.getInt(1);
                String book_name = ret.getString(2);
                Date buy_Date = ret.getDate(3);
                String book_num = ret.getString(4);
                String book_author = ret.getString(5);
                Float price = ret.getFloat(6);
                int count = ret.getInt(7);
                String catgory = ret.getString(8);  

                System.out.println("id = " + id  + "book_name=" + book_name + "buy_Date = " + buy_Date + "book_num = " + book_num + "book_author = " + book_author);
                BookListModel bookModel = new BookListModel();//创建Book对象  
                bookModel.setId(id);//设置id  
                bookModel.setBook_name(book_name);//设置name  
               bookModel.setBuy_Date(buy_Date);
               bookModel.setBook_num(book_num);
               bookModel.setBook_author(book_author);
               bookModel.setPrice(price);
               bookModel.setCount(count);
               bookModel.setCatgory(catgory);
                list.add(bookModel);//将students对象放置到列表中  

            }

        System.out.println("list" + list);
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }

5、运行时，一只报如下错误

 spring Could not load JDBC driver class [com.mysql.jdbc.Driver]

网上查询一直说是缺少包，但是实际上我的maven包中已经存在了。查了很多资料不是说缺少包，就是少配置，但是都存在。于是最终尝试了将mysql包删除，重新下载。结果就好了。呜呜我～～后来同事说可能是我的mysql是6.5的版本。mysql的版本低了。不匹配

6、先找到项目当前mysql的位置，找到后删除

在maven中找到mysql-connector….右击—Properties

7、更新maven，

8、再次运行，就可以看到控制台打印相关信息

9、运行后发现有如下问题：

WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.

解决方法一：若是我们在applicationContent.xml中配置的话，需要修改url

<?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 = "dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
         <property name="driverClassName"  value="com.mysql.jdbc.Driver" />
    <!-- <property name="url" value="jdbc:mysql://localhost:3306/new_schema"/>  -->

        <property name="url" value="jdbc:mysql://localhost:3306/new_schema?useUnicode=true&amp;characterEncoding=UTF-8&amp;useSSL=false" />

            <property name="username" value="root"/>  
            <property name="password" value="123456" />  
    </bean>

</beans>

解决方法二：在代码中配置的，如下

@SpringBootApplication
public class ConnectSqlApplication {

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

        Connection connection;
        try {
//          connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/new_schema", "root", "123456");

            connection =  DriverManager.getConnection("jdbc:mysql://localhost:3306/new_schema?useUnicode=true&characterEncoding=utf-8&useSSL=false","root","123456");

            PreparedStatement pStatement = connection.prepareStatement("select * from Booklist");

            ResultSet rSet = pStatement.executeQuery();
            while (rSet.next()) {
                System.err.println(rSet.getString("book_name"));
            }

            pStatement.close();

            connection.close();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

    }
}

注意：url中一定要加“characterEncoding=UTF-8”编码格式，不然你在查询条件是中文时，会出现查询不到的问题，但是数据库中实际是存在。这是今天偶然间测试出来的