MySQL安装与JDBC编程-优快云博客

一、安装mysql
mysql下载网址
注意事项：下载社区版commumunity的msi文件，双击该文件安装即可
选对话框选择custom，
并且后面之添加server的所有文件，其它按照标准格式下载

将数据库连接到navicat注意事项：
                  2059错误
                   1045错误
                 1045错误选择方法3，改密码的sql语句换一下不会报语法错误的sql语句

二、示范JDBC编程
1、数据库驱动
 ClassNotFoundException解决

package Capter13_mysql数据库与JDBC编程;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class ConnMysql {

    public static void main(String[] args) throws SQLException, ClassNotFoundException{

        String driverName = "com.mysql.jdbc.Driver";//com.mysql.cj.jdbc.Driver
        String url = "jdbc:mysql://127.0.0.1:3306/mytest";//localhost:3306
        String user = "root";
        String password = "guozi";

        Class.forName(driverName);//加载驱动
        Connection conn = DriverManager.getConnection(url, user, password);//建立与数据库的连接
        Statement stmt = conn.createStatement();//创建Statement对象

        String sql = " select * from t_extr_dict where property = \"产品总体评价\" and feature_type = \"正面\"";// 
        ResultSet rs = stmt.executeQuery(sql);

        while(rs.next()){
            System.out.println(rs.getInt("id")+"\t"+rs.getString("domain")+"\t"
                              +rs.getString("feature")+"\t"+rs.getString("feature_type"));
        }

        rs.close();
        stmt.close();
        conn.close();

    }

}

2、配置文件
数据库连接信息保存在properties：
eclipse中创建properties文件
 两种读取配置文件的方法

driverName = com.mysql.jdbc.Driver
url = jdbc:mysql://127.0.0.1:3306/mytest
user = root
password = ******

//数据库连接信息

package Capter13_mysql数据库与JDBC编程;

import java.io.BufferedInputStream;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;


public class GetConnection {

    private static GetConnection instance;//创建缓存单例的成员变量

    public static final String inputFile = "resource/sql/my_sql_test.peoperties";
    private static String driver;
    private static String url;
    private static String user;
    private static String password;

    public static Properties props ; 
    public static Connection conn;


    private GetConnection() {};//将构造函数隐藏起来
    public static GetConnection getInstance() throws IOException, ClassNotFoundException, SQLException{
        if(instance==null){
            instance=new GetConnection();//用一个public创建一个类对象，用成员变量缓存起来
        }
        getConnection();
        return instance;
    }

    public static void getConnection() throws IOException, ClassNotFoundException, SQLException{

        getFactory();
        Class.forName(driver);
        conn = DriverManager.getConnection(url, user, password);

        System.out.println("数据库连接成功");
    }


    public static  void getFactory() throws IOException{

        InputStream b = new BufferedInputStream(new FileInputStream(inputFile));
        props = new Properties();
        props.load(b);

        driver = props.getProperty("driverName");
        url = props.getProperty("url");
        user =props.getProperty("user");
        password = props.getProperty("password");   
    }






    public static Connection getConn() {
        return conn;
    }
    public static void setConn(Connection conn) {
        GetConnection.conn = conn;
    }



}

3、执行DDL语句、创建数据库

package Capter13_mysql数据库与JDBC编程;

import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class ExcuteDDL {

    static Connection conn;
    static PreparedStatement p_stmt;

    public static void main(String[] args) throws ClassNotFoundException, IOException, SQLException{
        String sql = "CREATE TABLE test("
                + "id int auto_increment primary key,"
                + "name varchar(255) not null,"
                + "age int not null"
                + ")charset=utf8;";
        getConnection(sql);
        System.out.println("建表成功");
        conn.close();
        p_stmt.close();
    }


    public static void getConnection(String sql) throws ClassNotFoundException, IOException, SQLException {
        GetConnection connection = GetConnection.getInstance();
        conn = connection.getConn();
        p_stmt = conn.prepareStatement(sql);
        p_stmt.executeUpdate();//可执行DML\DDL语句
    }

}

4、执行DML语句、操作数据库

package Capter13_mysql数据库与JDBC编程;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class excuteDML {

    static Connection conn;
    static PreparedStatement p_stmt;

    public static void main(String[] args) throws ClassNotFoundException, IOException, SQLException{
        String sql = "insert into test(name,age)"
                + "values(\"james\",15),"
                + "(\"john\",20);";
        getConnection(sql);
        System.out.println("数据插入成功");
        conn.close();
        p_stmt.close();
    }


    public static void getConnection(String sql) throws ClassNotFoundException, IOException, SQLException {

        GetConnection connection = GetConnection.getInstance();
        conn = connection.getConn();
        p_stmt = conn.prepareStatement(sql);
        p_stmt.executeUpdate();//可执行DML\DDL语句
    }

}

5、使用excute方法执行SQL语句

package Capter13_mysql数据库与JDBC编程;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class excuteSQL {

    static Connection conn;
    static PreparedStatement p_stmt;

    public static void main(String[] args) throws ClassNotFoundException, IOException, SQLException{
        String sql = "insert into test(name,age)"
                + "values(\"jamnif\",19),"
                + "(\"julie\",18);";

        boolean ok_or_not = getConnection(sql);

        System.out.println("操作数据库成功？："+ok_or_not);
        //false,但是数据库操作成功
        //因为只有第一个对象是ResultSet，才会返回true；如select操作
        //如果执行完后第一个结果是受影响的行数或其他情况，返回false

        if(ok_or_not){
            ResultSet rs = p_stmt.getResultSet();
            int count = p_stmt.getUpdateCount();

            for(int i=0;i<count;i++){
                System.out.println(rs.getInt("id")+"\t"+rs.getString("name")+"\t"+rs.getInt("age"));
            }
        }

        System.out.println("数据库操作完毕");

        p_stmt.close();
        conn.close();

    }

    public static boolean getConnection(String sql) throws ClassNotFoundException, IOException, SQLException {
        GetConnection connection = GetConnection.getInstance();
        conn = connection.getConn();
        p_stmt = conn.prepareStatement(sql);
        return p_stmt.execute();// 几乎可执行所有的SQL语句
    }



}

6、管理结果集

package Capter13_mysql数据库与JDBC编程;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class VarResultSet {

    static Connection conn;
    static PreparedStatement p_stmt;


    public static void main(String[] args) throws ClassNotFoundException, IOException, SQLException{

        String sql = "select * from test";
        ResultSet rs = getConnection(sql);

        rs.last();//将指针移动到最后一行
        int count = rs.getRow();//获得改行所处的行数
        for(int i=count;i>0;i--){
            rs.absolute(i);//将指针移动到改行
            System.out.println(rs.getString("name")+"\t"+rs.getInt("age"));
            rs.updateString(2, "学生名"+i);//修改指针所指行的特征列的值
            rs.updateRow();//提交修改
        }

        System.out.println("数据库操作完毕");

        p_stmt.close();
        conn.close();

    }

    public static ResultSet getConnection(String sql) throws ClassNotFoundException, IOException, SQLException {
        GetConnection connection = GetConnection.getInstance();
        conn = connection.getConn();

        //创建可滚动、可更新的结果集，默认的是静态的结果集
        p_stmt = conn.prepareStatement(sql,ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDATABLE);
        return p_stmt.executeQuery();// 可执行DML\DDL语句
    }

}