Java交互Mysql

Java交互Mysql

下载

下载mysql的java驱动jar包，mysql-connector-java_8.0.12，下载网址，这个是mysql官网的下载地址，根据系统下载相应的版本。

eclipse：工作目录下新建Folder ，把jar文件拖到工作目录，右键jar文件Build Path—>Add to Build Path 一下，项目就可以运行了。

idea：菜单栏选择File–>Project Structure–>Modules–>Dependencies–>新建一个JARs，指定你下载的驱动包。

代码

1、创建数据库

USE test;
CREATE TABLE `websites` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` char(20) NOT NULL DEFAULT '' COMMENT '站点名称',
  `url` varchar(255) NOT NULL DEFAULT '',
  `alexa` int(11) NOT NULL DEFAULT '0' COMMENT 'Alexa 排名',
  `country` char(10) NOT NULL DEFAULT '' COMMENT '国家',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;
-- 添加数据
INSERT INTO `websites` VALUES 
 ('1', 'Google', 'https://www.google.cm/', '1', 'USA'),
 ('2', '淘宝', 'https://www.taobao.com/', '13', 'CN'), 
 ('3', '京东', 'http://www.jd.com', '12', 'CN'),
 ('4', '微博', 'http://weibo.com/', '20', 'CN'), 
 ('5', 'Facebook', 'https://www.facebook.com/', '3', 'USA');

2、java交互mysql

import java.sql.*;

public class JavaConnMysql {
    private Connection conn = null;
    private Statement stmt = null;
    private ResultSet rs = null;

    private final String URL = "jdbc:mysql://localhost:3306/test?userSSL=false&characterEncoding=utf8&allowPublicKeyRetrieval=true&serverTimezone=UTC";
    private final String USER = "root";
    private final String PASSWORD = "123456";


    //加载驱动
    public void sqlconn() {
        try {
            //驱动加载：8.0以上："com.mysql.cj.jdbc.Driver"
            Class.forName("com.mysql.cj.jdbc.Driver");
            //"jdbc:mysql://localhost:3306/test?userSSL=false&allowPublicKeyRetrieval=true&serverTimezone=UTC";

            //加载驱动：8.0 以下："com.mysql.jdbc.Driver"
            // 链接地址"jdbc:mysql://localhost:3306/test		
            
       		//声明连接
            conn = DriverManager.getConnection(URL, USER, PASSWORD);

            stmt = conn.createStatement();//创建声明
			
			//添加  //删除和修改类似
            int a = stmt.executeUpdate("INSERT INTO websites VALUES (NULL ,'京东','http://www.jd.com','5623','CN')");
            if (a!=0){
				System.out.println("添加成功");
			}
            //查询
            rs = stmt.executeQuery("SELECT * FROM websites");
            while (rs.next()) {//获取查询的值
                int id = rs.getInt("id");
                String name = rs.getString("name");
                String url = rs.getString("url");
                String alexa = rs.getString("alexa");
                String country = rs.getString("country");

                System.out.println("ID："+id+"  "+"Name："+name
                        +"  "+"Url："+url+"  "+"Alexa："+alexa+"  "+"Country："+country
                );//打印输出
            }

            //关闭
            conn.close();
            stmt.close();
            rs.close();

        } catch (SQLException e2) {
            e2.printStackTrace();

        } catch (Exception e) {//解决加载驱动报错
            e.getMessage();
        } finally {
            //执行关闭
            try {
                if (conn != null) conn.close();//关闭连接声明
            } catch (SQLException e) {
            }

            try {
                if (stmt != null) stmt.close();//关闭创建的声明
            } catch (SQLException e) {
                e.getMessage();
            }
            
            try {
                if (rs != null) rs.close();//关闭结果集
            } catch (SQLException e) {
                e.getMessage();
            }
        }
    }
}