专业实习课day02

简单学习jdbc

一、环境介绍

• 在mysql中创建tb_user表，属性为id,username,password
• 新建一个java工程jdbc，并导入jar包

二、详细步骤

1.加载驱动

Class.forName("com.mysql.jdbc.Driver");

2.创建连接

Connection connection= null;
        try {
            connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/wzsxy","root","123456");
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return connection;

3.sql语句

（1）增

String sql="select * from tb_user";

（2）删

String sql="delete from tb_user where id=5";```

（3）改

update tb_user set username='wz' where id=5

（4）查

String sql="select * from tb_user";

4.获得statement对象

PreparedStatement statement=connection.prepareStatement(sql);

5.执行sql语句

增：ResultSet resultSet =statement.executeQuery();
删，改，查：statement.executeUpdate();

6.处理结果集

while (resultSet.next()){
                System.out.println("id："+resultSet.getInt(1));
                System.out.println("username："+resultSet.getString(2));
                System.out.println("password："+resultSet.getString(3));
            }

7.关闭资源

DBUtil.closeAll(resultSet,statement,connection);

完整代码

Find:

package wsw;

import wsw.util.DBUtil;

import java.sql.*;

public class Find {

    public static void main(String[] args) {
        //1.加载驱动
        //2.创建连
        Connection connection= null;
        try {
            connection = DBUtil.getConnection();
            //3.sql语句
            String sql="select * from tb_user";
            //4.获得statement对象
            PreparedStatement statement=connection.prepareStatement(sql);
            //5.执行sql
            ResultSet resultSet =statement.executeQuery();
            //6.处理结果集
            while (resultSet.next()){
                System.out.println("id："+resultSet.getInt(1));
                System.out.println("username："+resultSet.getString(2));
                System.out.println("password："+resultSet.getString(3));
            }
            //7.关闭资源
            DBUtil.closeAll(resultSet,statement,connection);
        } catch (SQLException e) {
            e.printStackTrace();
        }

    }
}

Insert:

package wsw;

import wsw.util.DBUtil;

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

public class Insert {
    public static void main(String[] args)  {
        Connection connection= null;
        try {
            connection = DBUtil.getConnection();
            //sql语句
            String sql="insert into tb_user(id,username,password) values(5,'wangwu','456')";
            //获得statment对象
            PreparedStatement statement=connection.prepareStatement(sql);
            //执行sql语句
            statement.executeUpdate();
            //关闭资源
            DBUtil.closeAll(null,statement,connection);
        } catch (SQLException e) {
            e.printStackTrace();
        }



    }
}

Update:

package wsw;

import wsw.util.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Collection;


public class Update {
    public static void main(String[] args) {
        try {
            //连接
            Connection connection=DBUtil.getConnection();
            //sql语句
            String sql="update tb_user set username='wz' where id=5";
            //获得statement对象
            PreparedStatement statement=connection.prepareStatement(sql);
            //执行sql
            statement.executeUpdate();
            //关闭资源
            DBUtil.closeAll(null,statement,connection);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Delete:

package wsw;

import wsw.util.DBUtil;

import java.sql.*;

public class Delete {
    public static void main(String[] args)  {
        //1.加载驱动
        //2.创建连
        Connection connection= null;
        try {
            connection = DBUtil.getConnection();
            //3.sql语句
            String sql="delete from tb_user where id=5";
            //4.获得statement对象
            PreparedStatement statement=connection.prepareStatement(sql);
            //5.执行sql
            statement.executeUpdate();
            //7.关闭资源
            DBUtil.closeAll(null,statement,connection);
        } catch (SQLException e) {
            e.printStackTrace();
        }

    }
}

DBUtil:

package wsw.util;

import java.sql.*;

public class DBUtil {
    public static Connection getConnection() {
        //1.加载驱动
        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        //2.创建连
        Connection connection= null;
        try {
            connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/wzsxy","root","123456");
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return connection;
    }

    public static void closeAll(ResultSet resultSet, Statement statement,Connection connection) throws SQLException {
        if (resultSet!=null){
            resultSet.close();
        }
        if (statement!=null){
            statement.close();
        } if (connection!=null){
            connection.close();
        }
    }
}