连接的数据库为本地数据库，库名：fortest 表名：news3 ，表内容如下：
1.解决 mysql远程连接报错： Host * is not allowed to connect to this MySQL server
在本机登入mysql后，更改“mysql”数据库里的“user”表里的“host”项，从”localhost”改为'%'。
use mysql; update user set host ='%'where user ='root' and host ='localhost'; flush privileges;2.构建maven依赖
<dependencies> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>5.1.29</version> </dependency> </dependencies>3.连接mysql，默认进入连接数据库的DATABASE为fortest，用户名root ，密码：123456
package com.jia.connect; import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; public class sqlConnect { /** * * @return Connection */ public static Connection getSqlConnection() { Connection sqlClient = null; try { // 1.加载数据访问驱动 Class.forName("com.mysql.jdbc.Driver"); //2.连接到数据"库"上去 sqlClient = DriverManager .getConnection("jdbc:mysql://localhost:3306/fortest?useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC", "root", "123456"); System.out.println("mysql连接成功，获取连接对象： " + sqlClient); } catch (ClassNotFoundException e) { // TODO Auto-generated catch block e.printStackTrace(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } return sqlClient; } }单个查询版本
package com.jia; import com.jia.connect.sqlConnect; import java.sql.Connection; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; public class Test { public static void main(String[] args) throws Exception{ Connection connection = sqlConnect.getSqlConnection(); Statement statement = connection.createStatement(); String sql = "select * from fortest.news3 where author = \"Alina Shrourou, B.Sc. (Editor)\""; //ResultSet类，用来存放获取的结果集！！ ResultSet rs = statement.executeQuery(sql); while (rs.next()){ String title = rs.getString("title"); System.out.println(title); } rs.close(); statement.close(); connection.close(); } }增删改查：
package com.jia; import com.jia.connect.sqlConnect; import java.sql.*; import java.util.ArrayList; public class Test { public static void main(String[] args) throws Exception{ Connection connection = sqlConnect.getSqlConnection(); Statement statement = connection.createStatement(); //ArrayList<String> result = search(statement); //delete(statement); //insertData(connection); statement.close(); connection.close(); } /** * 根据news_id 删除数据，删除成功输出1，否则输出0 * @param statement * @throws SQLException */ public static void delete(Statement statement) throws SQLException { String sql2 = "delete from fortest.news3 where news_id = 2999"; int rs2 = statement.executeUpdate(sql2); System.out.println(rs2); } /** * 根据条件查询 * @param statement * @return 包含查询结果的一个list * @throws SQLException */ public static ArrayList search(Statement statement) throws SQLException{ ArrayList<String> result = new ArrayList<>(); String sql = "select * from fortest.news3 where news_id <200"; ResultSet resultSet = statement.executeQuery(sql); while (resultSet.next()){ String news_id = resultSet.getString(0); String title = resultSet.getString(1); String date = resultSet.getString(2); result.add(news_id); result.add(title); result.add(date); } resultSet.close(); return result; } /** * 插入一条数据 * @param connection * @throws SQLException */ public static void insertData (Connection connection) throws SQLException { String sql = "insert into fortest.news3 values(?,?,?,?,?,?,?)"; Date date = new Date(new java.util.Date().getTime()); PreparedStatement statement = connection.prepareStatement(sql); statement.setInt(1,3000); statement.setString(2,"测试"); statement.setDate(3,date); statement.setString(4,"JWW"); statement.setString(5,""); statement.setString(6,""); statement.setString(7,""); int result = statement.executeUpdate(); System.out.println(result); statement.close(); } }
