Java 基础之JDBC操作数据库

首先要下载JDBC驱动并且导入

1.连接数据库

import java.sql.*;

public class Conn {

    Connection con;
    public Connection getConnection()
    {
        try {
            Class.forName("com.mysql.jdbc.Driver");
            System.out.println("数据库驱动加载成功");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }

        try {
            con=DriverManager.getConnection("jdbc:mysql://localhost:3306/pets","root","157520");
            System.out.println("数据库连接成功");

        } catch (SQLException e) {
            e.printStackTrace();
        }
        return con;
    }
    
    public static void main(String[] args)
    {
        Conn c=new Conn();
        Connection con=c.getConnection();
    }
}

运行结果：

 

执行查询语句：

1.建立连接

2.建立语句

3.执行查询

import java.sql.*;

public class Conn {

    Connection con;

    public Connection getConnection() {
        try {
            Class.forName("com.mysql.jdbc.Driver");
            System.out.println("数据库驱动加载成功");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }

        try {
            con = DriverManager.getConnection("jdbc:mysql://localhost:3306/pets", "root", "157520");
            System.out.println("数据库连接成功");

        } catch (SQLException e) {
            e.printStackTrace();
        }
        return con;
    }

    public static void main(String[] args) {
        Conn c = new Conn();
        Connection con = c.getConnection();


        Statement statement = null;
            try {
                statement = con.createStatement();

                //cat表
                System.out.println("Cats表");
                ResultSet res = statement.executeQuery("select * from cats");

                while (res.next()) {
                    String name = res.getString("name");
                    String owner = res.getString("owner");
                    String birth = res.getString("birth");
                    System.out.println(name + " " + owner + " " + birth);
                }

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

运行结果：

 

预处理语句（preparedStatement）

案例：

import java.sql.*;

public class Conn {

    Connection con;

    public Connection getConnection() {
        try {
            Class.forName("com.mysql.jdbc.Driver");
            System.out.println("数据库驱动加载成功");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }

        try {
            con = DriverManager.getConnection("jdbc:mysql://localhost:3306/pets", "root", "157520");
            System.out.println("数据库连接成功");

        } catch (SQLException e) {
            e.printStackTrace();
        }
        return con;
    }

    public static void main(String[] args) {
        Conn c = new Conn();
        Connection con = c.getConnection();

        PreparedStatement sql=null;

        try {
            sql=con.prepareStatement("select * from cats where id = ?");
            sql.setInt(1,1);
            ResultSet res=sql.executeQuery();

            while (res.next())
            {
                String name=res.getString("name");
                String owner=res.getString("owner");
                String birth=res.getString("birth");
                System.out.println(name+" "+owner+" "+birth);
            }

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

    }
    }

运行结果：

 

增删改查：

import java.sql.*;

public class Conn {

    Connection con;

    public Connection getConnection() {
        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }

        try {
            con = DriverManager.getConnection("jdbc:mysql://localhost:3306/pets", "root", "157520");

        } catch (SQLException e) {
            e.printStackTrace();
        }
        return con;
    }

    public static void main(String[] args) {
        Conn c = new Conn();
        Connection con = c.getConnection();

        PreparedStatement sql=null;

        try {
            sql=con.prepareStatement("select * from cats");
            ResultSet res=sql.executeQuery();

            System.out.println("执行前数据：");
            while (res.next())
            {
                String name=res.getString("name");
                String owner=res.getString("owner");
                String birth=res.getString("birth");
                System.out.println(name+" "+owner+" "+birth);
            }

            //删除数据
            sql=con.prepareStatement("delete from cats where id=?");
            sql.setInt(1,1);
            sql.executeUpdate();

            //更改数据
            sql=con.prepareStatement("update cats set name=? where id=2");
            sql.setString(1,"MC");
            sql.executeUpdate();


            sql=con.prepareStatement("select * from cats");
            ResultSet res1=sql.executeQuery();


            System.out.println("执行改删后数据：");
            while (res1.next())
            {
                String name=res1.getString("name");
                String owner=res1.getString("owner");
                String birth=res1.getString("birth");
                System.out.println(name+" "+owner+" "+birth);
            }


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

    }
}

 

运行结果：