code总结——java连接mysql

java连接mysql

连接的数据库为本地数据库，库名：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();
    }
}