JDBC

JDBC

Java database connection
连接数据库和java

SQLyog新建连接
新建数据库tzy
建表people
导入jar包

连接mysql

 //1.加载驱动
            Class.forName("com.mysql.jdbc.Driver");
            //2.创建连接
            connection = DriverManager.getConnection
                    ("jdbc:mysql://127.0.0.1:3306/tzy?useSSL=true&characterEncoding=utf-8&user=root&password=123");
            System.out.println("创建连接成功");

查询

          //3.写sql
          String sql="select * from people";
          //4.得到statement对象
          statement = connection.prepareStatement(sql);
          //5.执行sql得到结果集
          rs = statement.executeQuery();
          //6.处理结果集
          while (rs.next()){
              System.out.println(rs.getInt(1));
              System.out.println(rs.getString(2));
              System.out.println(rs.getString(3));
          }

输出

插入

     //3.写sql
            String sql="INSERT INTO people(name,password)values(?,?)";
            //4.得到statement对象
            statement = connection.prepareStatement(sql);
            statement.setString(1,"suangcai");
            statement.setString(2,"jianglaoshi");
            statement.executeUpdate();

输出

[user{id=1, username='tzy', password='123'}, user{id=2, username='abc', password='456'}, user{id=3, username='nnn', password='789'}, user{id=7, username='suangcai', password='jianglaoshi'}]

封装

先封装建立连接

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/tzy?useSSL=true&characterEncoding=utf-8&user=root&password=123");
        } 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();
        }
    }
}

将打印和插入封装为findAll()和add()方法，建立Dao包创建userdao类，把他们放进去

    public List<user> findAll(){
        ResultSet rs=null;
        PreparedStatement statement=null;
        Connection connection=null;
        List<user> list=new ArrayList<>();
        try {
            connection= DButil.getConnection();
            //3.写sql
            String sql="select * from people";
            //4.得到statement对象
            statement = connection.prepareStatement(sql);
            //5.执行sql得到结果集
            rs = statement.executeQuery();
            //6.处理结果集
            while (rs.next()){
                user user=new user();
                user.setId(rs.getInt(1));
                user.setUsername(rs.getString(2));
                user.setPassword(rs.getString(3));
                list.add(user);
            }

            //7.关闭资源

        } catch (Exception e) {
            e.printStackTrace();
        }
        finally
        {
            DButil.close(rs,statement,connection);
        }
        return list;
    }

add类

    public void add(){

        PreparedStatement statement=null;
        Connection connection=null;
        try {

            connection = DButil.getConnection();

            //3.写sql
            String sql="INSERT INTO people(name,password)values(?,?)";
            //4.得到statement对象
            statement = connection.prepareStatement(sql);
            statement.setString(1,"suangcai");
            statement.setString(2,"jianglaoshi");
            statement.executeUpdate();
            //6.处理结果集

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

最后主类

public class Test {
    public static void main(String[] args) {
        userdao userdao=new userdao();
        List<user> list = userdao.findAll();
        System.out.println(list);
        userdao.add();
        List<user> list1 = userdao.findAll();
        System.out.println(list1);
    }
}

完了