JDBC操作

查找mysql进程号和端口号：

[root@master ~]# ps -aux | grep mysql
mysql     10641  0.2 11.4 1734020 213884 ?      Sl   3月10   4:55 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid
root      45256  0.0  0.0 112828   976 pts/0    S+   19:24   0:00 grep --color=auto mysql
[root@master ~]# netstat -tnlp | grep 10641
tcp6       0      0 :::3306                 :::*                    LISTEN      10641/mysqld   

package com.shujia.maven.jdbc;
 
import java.sql.*;
 
public class Demo1MysqlJdbc {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        //1.加载驱动
        Class.forName("com.mysql.jdbc.Driver");
        //2.建立连接
        /**
         * jdbc url ===> jdbc:mysql://master:3306/db1?useSSl=false
         */
        Connection conn = DriverManager.getConnection("jdbc:mysql://master:3306/db2?useSSl=false", "root", "123456");
        //3.创建statement
        Statement st = conn.createStatement();
       // st.executeQuery();//执行DQl
       // st.execute();//执行DDL,DCL
        // st.executeUpdate();//执行DML
        //查询 年龄 =23 的学生
        int age=23;
        ResultSet rs = st.executeQuery("select * from student where age =" + age);
        //4.如果有数据返回则可以遍历ResultSet
        while (rs.next()){
            int id = rs.getInt("id");
            String name = rs.getString("name");
            int stu_age = rs.getInt("age");
            String gender = rs.getString("gender");
            String clazz = rs.getString("clazz");
            System.out.println(id+","+name+","+age+","+gender+","+clazz);
        }
        //关闭连接
        conn.close();
    }
}


package com.shujia.maven.jdbc;
 
import java.sql.*;
 
public class Demo1MysqlJdbc {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        //1.加载驱动
        Class.forName("com.mysql.jdbc.Driver");
        //2.建立连接
        /**
         * jdbc url ===> jdbc:mysql://master:3306/db1?useSSl=false
         */
        Connection conn = DriverManager.getConnection("jdbc:mysql://master:3306/db2?useSSl=false", "root", "123456");
        //3.创建statement
        Statement st = conn.createStatement();
       // st.executeQuery();//执行DQl
       // st.execute();//执行DDL,DCL
        // st.executeUpdate();//执行DML
        //查询 年龄 =23 的学生
        System.out.println(args[0]);
        String age=args[0];
        ResultSet rs = st.executeQuery("select * from student where age =" + age);
        //4.如果有数据返回则可以遍历ResultSet
        while (rs.next()){
            int id = rs.getInt("id");
            String name = rs.getString("name");
            int stu_age = rs.getInt("age");
            String gender = rs.getString("gender");
            String clazz = rs.getString("clazz");
            System.out.println(id+","+name+","+age+","+gender+","+clazz);
        }
        //关闭连接
        conn.close();
    }
}

package com.shujia.maven.jdbc;
 
import java.sql.*;
 
public class Demo1MysqlJdbc {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        //1.加载驱动
        Class.forName("com.mysql.jdbc.Driver");
        //2.建立连接
        /**
         * jdbc url ===> jdbc:mysql://master:3306/db1?useSSl=false
         */
        Connection conn = DriverManager.getConnection("jdbc:mysql://master:3306/db2?useSSl=false", "root", "123456");
        //3.创建statement
        Statement st = conn.createStatement();
       // st.executeQuery();//执行DQl
       // st.execute();//执行DDL,DCL
        // st.executeUpdate();//执行DML
        //查询 年龄 =23 的学生
        //如果用户输入"22 or 1=1 ",那么就会造成数据泄露的问题
        //使用statement容易出现sql注入问题
        System.out.println(args[0]);
        String age=args[0];
        //ResultSet rs = st.executeQuery("select * from student where age =" + age);
        //使用prepareStatement避免注入问题
        PreparedStatement preSt = conn.prepareStatement("select * from student where age =?");
        /**
         * 参数的顺序 从 1开始数
         */
        preSt.setInt(1,Integer.parseInt(age));
        ResultSet rs=preSt.executeQuery();
        //4.如果有数据返回则可以遍历ResultSet
        while (rs.next()){
            int id = rs.getInt("id");
            String name = rs.getString("name");
            int stu_age = rs.getInt("age");
            String gender = rs.getString("gender");
            String clazz = rs.getString("clazz");
            System.out.println(id+","+name+","+age+","+gender+","+clazz);
        }
        //关闭连接
        conn.close();
    }
}

package com.shujia.maven.jdbc;
 
 
import java.io.BufferedReader;
import java.io.FileReader;
import java.io.IOException;
import java.sql.*;
 
public class Demo2InsertIntoMysql {
    public static void main(String[] args) throws ClassNotFoundException, SQLException, IOException {
        //加载驱动
        Class.forName("com.mysql.jdbc.Driver");
        //创建连接
        Connection conn = DriverManager.getConnection("jdbc:mysql://master:3306/db2?useSSl=false", "root", "123456");
        //创建Statement
        Statement st = conn.createStatement();
        st.execute("create table if not exists student_like like student");
        //创建prepareStatement
        PreparedStatement preSet = conn.prepareStatement("insert into student_like values(?,?,?,?,?)");
        //读取数据
        BufferedReader br =new BufferedReader(new FileReader("data/students.txt")) ;
        String line;
        while((line=br.readLine())!=null){
            String[] splits=line.split(",");
            int id= Integer.parseInt(splits[0]);
            String name=splits[1];
            int age= Integer.parseInt(splits[2]);
            String gender=splits[3];
            String clazz=splits[4];
            //设置参数
            preSet.setInt(1,id);
            preSet.setString(2,name);
            preSet.setInt(3,age);
            preSet.setString(4,gender);
            preSet.setString(5,clazz);
            //执行插入
            preSet.executeUpdate();
        }
        //关闭连接
        st.close();
        preSet.close();
        conn.close();
    }
}