JDBC学习第一天

SQLyog数据库

安装后直接运行，打开文件—新连接，接下来都是大同小异，连接名称，主机IP，用户名和密码这些。
然后测试连接，成功后进入数据库和表结构页面，可以进行一系列的操作了。

IDEA增删查改

项目结构
UserInfo.class
package com.zhongruan.bean;

public class UserInfo {
private int id;
private String username;
private String password;

public int getId() {
    return id;
}

public void setId(int id) {
    this.id = id;
}

public String getUsername() {
    return username;
}

public void setUsername(String username) {
    this.username = username;
}

public String getPassword() {
    return password;
}

public void setPassword(String password) {
    this.password = password;
}

@Override
public String toString() {
    return "UserInfoDao{" +
            "id=" + id +
            ", username='" + username + '\'' +
            ", password='" + password + '\'' +
            '}';
}

}

UserInfoDao.class
package com.zhongruan.dao;

import com.zhongruan.bean.UserInfo;
import com.zhongruan.util.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

public class UserInfoDao {
public List findAll(){
ResultSet rs=null;
PreparedStatement statement=null;
Connection connection=null;
List list=new ArrayList<>();
try {
connection= DBUtil.getconnection();
System.out.println(“创建连接成功”);
//3.写sql
String sql=“select * from userinfo”;
//4.得到statement对象
statement = connection.prepareStatement(sql);
//5.执行sql得到结果集
rs = statement.executeQuery();
//6.处理结果集
while (rs.next()){
UserInfo userInfo=new UserInfo();
userInfo.setId(rs.getInt(1));
userInfo.setUsername(rs.getString(2));
userInfo.setUsername(rs.getString(3));

            list.add(userInfo);
        }
        //7.关闭资源
    } catch (Exception e) {
        e.printStackTrace();
    }finally {
        DBUtil.close(rs,statement,connection);


    }

    return list;
}

public static void  add(){
    PreparedStatement statement=null;
    Connection connection=null;
    try{
        connection=DBUtil.getconnection();
        String sql="insert into userinfo(username,password) values(?,?)";
        statement=connection.prepareStatement(sql);
        statement.setString(1,"tt");
        statement.setString(2,"dd");

        statement.executeUpdate();
    }catch (Exception e){
        e.printStackTrace();
    }finally {
        DBUtil.close(null,statement,connection);
    }
}

}

DBUtil.class
package com.zhongruan.util;

import jdk.internal.dynalink.beans.StaticClass;

import java.sql.*;

public class DBUtil {
public static Connection getconnection(){

    Connection connection=null;
    try {
        //1.加载驱动
        Class.forName("com.mysql.jdbc.Driver");
        //2.创建连接
        connection = DriverManager.getConnection
                ("jdbc:mysql://127.0.0.1:3306/user?useSSL=true&" +
                        "characterEncoding=utf-8&user=" +
                        "root&password=123456");

    } catch (Exception e) {
        e.printStackTrace();
    }
 return connection;
}

 public static  void close(ResultSet rs,Statement statement,
                          Connection connection){
     if(rs!=null){
         try {
             rs.close();
         } catch (SQLException e) {
             e.printStackTrace();
         }
     }
     if(statement!=null){
         try {
             statement.close();
         } catch (SQLException e) {
             e.printStackTrace();
         }
     }
     if(connection!=null){
         try {
             connection.close();
         } catch (SQLException e) {
             e.printStackTrace();
         }
     }
 }

}

insert.class
package com.zhongruan.util;

import com.zhongruan.bean.UserInfo;
import com.zhongruan.dao.UserInfoDao;
import com.zhongruan.util.DBUtil;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.List;

public class Insert {
public static void main(String[] args) {
UserInfoDao userInfoDao=new UserInfoDao();

    List<UserInfo> list=userInfoDao.findAll();
    System.out.println(list);
    userInfoDao.add();
    List<UserInfo> list1=userInfoDao.findAll();
    System.out.println(list1);
}

}
Test.class
package com.zhongruan.util;

import com.zhongruan.bean.UserInfo;
import com.zhongruan.dao.UserInfoDao;
import com.zhongruan.util.DBUtil;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class Test {
public static void main(String[] args) {
UserInfoDao userInfoDao=new UserInfoDao();

    List<UserInfo> list=userInfoDao.findAll();
    System.out.println(list);

}

}

运行结果