JavaWeb_Jdbc操作

JavaWeb_Jdbc操作

1.导入jar包

<dependencies>
<!--        mysql的驱动-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.47</version>
        </dependency>
    </dependencies>

2.配置sql

CREATE TABLE users(
	id INT PRIMARY KEY,
	`name` VARCHAR(40),
	`password` VARCHAR(40),
	email VARCHAR(60),
	birthday DATE 
);

INSERT INTO users(id,`name`,`password`,email,birthday)
VALUES(1,'张三','123456','zs@qq.com','2000-01-01');
INSERT INTO users(id,`name`,`password`,email,birthday)
VALUES(2,'李四','123456','ls@qq.com','2000-01-01');
INSERT INTO users(id,`name`,`password`,email,birthday)
VALUES(3,'王五','123456','ww@qq.com','2000-01-01');

SELECT * FROM users;

3.jdbc普通编程

package com.king.test;

import java.sql.*;

public class TestJdbc {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        //配置信息
        //useUnicode=true&characterEncoding=utf8解决中文乱码
        String url = "jdbc:mysql://localhost:3306/jdbc?useUnicode=true&characterEncoding=utf8";
        String username = "root";
        String password = "123456";

        //1.加载驱动
        Class.forName("com.mysql.jdbc.Driver");
        //2.连接数据库,代表数据库
        Connection connection = DriverManager.getConnection(url, username, password);
        //3.向数据库发送SQL的对象Statement
        Statement statement = connection.createStatement();
        //4.编写SQL
        String sql = "select * from jdbc.users";
        //5.执行查询SQL,返回一个ResultSet:结果集
        ResultSet rs = statement.executeQuery(sql);

        while (rs.next()){
            System.out.println("id="+rs.getObject("id"));
            System.out.println("id="+rs.getObject("name"));
            System.out.println("id="+rs.getObject("password"));
            System.out.println("id="+rs.getObject("email"));
            System.out.println("id="+rs.getObject("birthday"));
        }
        //6.关闭连接，释放资源(先开后关)
        rs.close();
        statement.close();
        connection.close();

    }
}

4.jdbc预编程

package com.king.test;

import java.sql.*;

public class TestJdbc2 {
    public static void main(String[] args) throws Exception{
        //配置信息
        //useUnicode=true&characterEncoding=utf8解决中文乱码
        String url = "jdbc:mysql://localhost:3306/jdbc?useUnicode=true&characterEncoding=utf8";
        String username = "root";
        String password = "123456";

        //1.加载驱动
        Class.forName("com.mysql.jdbc.Driver");
        //2.连接数据库,代表数据库
        Connection connection = DriverManager.getConnection(url, username, password);
        //3.编写SQL
        String sql = "insert into  jdbc.users(id, name, password, email, birthday) values (?,?,?,?,?);";
        //4.预编程
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setInt(1,4);
        preparedStatement.setString(2,"king");
        preparedStatement.setString(3,"123456");
        preparedStatement.setString(4,"xb@qq.com");
        preparedStatement.setDate(5,new Date(new java.util.Date().getTime()));

        //5.执行SQL
        int i = preparedStatement.executeUpdate();
        if (i>0){
            System.out.println("插入成功！");
        }

        //6.关闭连接，释放资源(先开后关)
        preparedStatement.close();
        connection.close();

    }
}