java jdbc in list_javaweb_JDBC

1：注册驱动(需拷入相关jar包)

2：获取连接Connection

3：得到执行sql语句的对象Statement

4：执行sql语句，并返回结果

5：处理结果

6：关闭Connection

代码示例：

packagemy;importjava.sql.Connection;importjava.sql.DriverManager;importjava.sql.ResultSet;importjava.sql.Statement;public classmyjdbc {

public static void main(String[] args) throwsException {//1 注册驱动

Class.forName("com.mysql.jdbc.Driver");//2 获取连接Connection(mydatabase：数据库名称，root:登录数据库用户名，123456：密码)

Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/mydatabase","root", "123456");//3 得到执行sql语句的对象Statement

Statement stmt =conn.createStatement();//4 执行sql语句，并返回结果(从表t_user中查找 id,name,password,email,birthday)

ResultSet rs = stmt.executeQuery("select id,name,password,email,birthday from t_user");//5 处理结果

while(rs.next()){

System.out.println(rs.getObject("id"));

System.out.println(rs.getObject("name"));

System.out.println(rs.getObject("password"));

System.out.println(rs.getObject("email"));

System.out.println(rs.getObject("birthday"));

System.out.println("---------------------------");

}//6 关闭Connection

rs.close();

stmt.close();

conn.close();

}

}

# 利用JDBC实现对数据库的增删改查

// 增加数据

public void testInsert() throwsException{//注册驱动

Class.forName("com.mysql.jdbc.Driver");//获取连接Connection

Connection conn = DriverManager.getConnection("dbc:mysql://localhost:3306/mydatabase","root", "123456");//得到执行sql语句的对象statement

Statement stmt =conn.createStatement();//执行sql语句，并得到返回结果(当返回的结果大于0时，表示插入数据成功)

int flag = stmt.executeUpdate("insert into t_user(name,password,email,birthday) values ('cat','123456','cat@163.com','1993-11-11')");if(flag > 0){

System.out.println("成功");

}//关闭资源

stmt.close();

conn.close();

}

// 更新数据

public void testDelete() throwsException{//注册驱动

Class.forName("com.mysql.jdbc.Driver");//获取连接Connection

Connection conn = DriverManager.getConnection("dbc:mysql://localhost:3306/mydatabase","root", "123456");//得到执行sql语句的对象statement

Statement stmt =conn.createStatement();//执行sql语句，并得到返回结果

int flag = stmt.executeUpdate("delete from t_user where name='cat'");if(flag > 0){

System.out.println("成功");

}//关闭资源

stmt.close();

conn.close();

}

# 注：上方两个程序存在的问题：如果在"执行sql语句，并返回结果"时发生异常，程序就会停止，而前面所得资源还未关闭，所以可以将关闭资源得代码放在funally中，具体修改代码如下

public static voidmain(String[] args) {

Connection conn= null;

Statement stmt= null;

ResultSet rs= null;//注册驱动

try{

Class.forName("com.mysql.jdbc.Driver");//获取连接Connection

conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/mydatabase", "root","12456");//得到执行sql语句的对象Statement

stmt =conn.createStatement();//执行sql语句，并返回结果

rs = stmt.executeQuery("select id,name,password,email,birthday from t_user");//处理结果

while(rs.next()) {

System.out.println(rs.getObject("id"));

System.out.println(rs.getObject("name"));

System.out.println(rs.getObject("password"));

System.out.println(rs.getObject("email"));

System.out.println(rs.getObject("birthday"));

System.out.println("------------");

}

}catch(ClassNotFoundException e) {

e.printStackTrace();

}catch(SQLException e) {

e.printStackTrace();

}finally{//关闭Connection

try{if(rs != null){

rs.close();

}

rs= null;if(stmt != null){

stmt.close();

}

stmt= null;if(conn != null){

conn.close();

}

conn= null;

}catch(SQLException e) {

e.printStackTrace();

}

}

}

}

// 查看数据

# 查找数据库文件public voidtestSelect(){

Connection conn= null;

Statement stmt= null;

ResultSet rs= null;try{//注册驱动

Class.forName("com.mysql.jdbc.Driver");//获取连接Connection

conn = DriverManager.getConnection("dbc:mysql://localhost:3306/mydatabase","root", "123456");//得到执行sql语句的对象Statement

stmt =conn.createStatement();//执行sql语句，并返回结果

rs = stmt.executeQuery("select id,name,password,email,birthday from t_user");//处理结果

List userList = new ArrayList<>();//User u = new User(); 如果将创建用户对象放于循环外，就指的是创建一个用户对象，这个用户对象被不断的赋值，

在List中添加的每一个数据对象，都是指向了同一个用户对象，它们的值都是最后一个添加的数据的值

while(rs.next()) {

User u= new User();//每循环一次创建一个对象，后依据下方代码给对象赋值，后将被赋值的用户对象添加到userList中

u.setId(rs.getInt("id"));

u.setName(rs.getString("name"));

u.setPassword(rs.getString("password"));

u.setEmail(rs.getString("email"));

u.setBirthday(rs.getDate("birthday"));

userList.add(u);//将数据存储到List中

}

System.out.println(userList);

}catch(ClassNotFoundException e) {

e.printStackTrace();

}catch(SQLException e) {

e.printStackTrace();

}finally{//关闭Connection

try{if(rs != null){

rs.close();

}if(stmt != null){

stmt.close();

}if(conn != null){

conn.close();

}

}catch(SQLException e) {

e.printStackTrace();

}

}

}//User.java文件

public classUser {private intid;privateString name;privateString password;privateString email;privateDate birthday;public intgetId() {returnid;

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

}publicString getName() {returnname;

}public voidsetName(String name) {this.name =name;

}publicString getPassword() {returnpassword;

}public voidsetPassword(String password) {this.password =password;

}publicString getEmail() {returnemail;

}public voidsetEmail(String email) {this.email =email;

}publicDate getBirthday() {returnbirthday;

}public voidsetBirthday(Date birthday) {this.birthday =birthday;

}

@OverridepublicString toString() {return "User [id=" + id + ", name=" + name + ", password=" + password + ", email=" + email + ",

birthday="+ birthday + "]";

}

}

# jdk7 和 JDBC4.1后正确关闭资源的方式

@Testpublic voidtestSelect() {//注册驱动

try{

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

}catch(ClassNotFoundException e) {

e.printStackTrace();

}//获取连接Connection

try (Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/mydatabase", "root","123456");//得到执行sql语句的对象Statement

Statement stmt =conn.createStatement();//执行sql语句，并返回结果

ResultSet rs = stmt.executeQuery("select id,name,password,email,birthday from t_user")) {//处理结果

List userList = new ArrayList<>();while(rs.next()) {

User u= newUser();

u.setId(rs.getInt("id"));

u.setName(rs.getString("name"));

u.setPassword(rs.getString("password"));

u.setEmail(rs.getString("email"));

u.setBirthday(rs.getDate("birthday"));

userList.add(u);

}

System.out.println(userList);

}catch(SQLException e) {

e.printStackTrace();

}

}

}

# 在配置文件中存储JDBC相关配置信息

//新建配置文件(src下创建)db.properties

driverClass=com.mysql.jdbc.Driver

url=jdbc:mysql://localhost:3306/mydatabase

username=root

password=123456

//加载配置文件(创建一个工具类)public classDBUtil {private staticString driverClass;private staticString url;private staticString username;private staticString password;static{

ResourceBundle rb= ResourceBundle.getBundle("db");

driverClass= rb.getString("driverClass");

url= rb.getString("url");

username= rb.getString("username");

password= rb.getString("password");try{//注册驱动

Class.forName(driverClass);

}catch(ClassNotFoundException e) {

e.printStackTrace();

}

}public static Connection getConnection() throwsSQLException{returnDriverManager.getConnection(url, username, password);

}

}

# 改造后的查看结果程序

public voidtestSelect() {//获取连接Connection

try(

Connection conn=DBUtil.getConnection();//得到执行sql语句的对象Statement

Statement stmt =conn.createStatement();//执行sql语句，并返回结果

ResultSet rs = stmt.executeQuery("select id,name,password,email,birthday from t_user")) {//处理结果

List userList = new ArrayList<>();while(rs.next()) {

User u= newUser();

u.setId(rs.getInt("id"));

u.setName(rs.getString("name"));

u.setPassword(rs.getString("password"));

u.setEmail(rs.getString("email"));

u.setBirthday(rs.getDate("birthday"));

userList.add(u);

}

System.out.println(userList);

}catch(SQLException e) {

e.printStackTrace();

}

}

# 模拟用户进行登录

//模拟登录

public classLoginService {publicUser findUserByNameAndPassword(String name, String password) {

User u= null;

String sql= "select id,name,password,email,birthday from t_user where name='" + name + "' and password='" + password + "'";

System.out.println(sql);//获取连接Connection

try (Connection conn =DBUtil.getConnection();//得到执行sql语句的对象Statement

Statement stmt =conn.createStatement();//执行sql语句，并返回结果

ResultSet rs =stmt.executeQuery(sql)) {//处理结果

while(rs.next()) {

u= newUser();

u.setId(rs.getInt("id"));

u.setName(rs.getString("name"));

u.setPassword(rs.getString("password"));

u.setEmail(rs.getString("email"));

u.setBirthday(rs.getDate("birthday"));

}

}catch(SQLException e) {

e.printStackTrace();

}returnu;

}

}//输入用户名和密码

public classLogin {public static voidmain(String[] args) {

Scanner input= newScanner(System.in);

System.out.println("请输入用户名：");

String name=input.nextLine();

System.out.println("请输入密码：");

String password=input.nextLine();//LoginService ls = new LoginService();

LoginServiceNew ls = newLoginServiceNew();

User u=ls.findUserByNameAndPassword(name, password);if(u == null){

System.out.println("用户名或密码错误!");

}else{

System.out.println("登录成功!");

}

}

}

# 模拟用户登录存在的问题 (sql注入问题)

当登陆时输入

用户名

user1

密码

hello' or 1='1此时的sql语句为"select id,name,password,email,birthday from t_user where name='user1' and password='hello' or 1='1'"注意看后面的or1='1' //当前面的用户名和密码对不上时为false，然而后面的1='1'的值都是true所以还是可以查出结果的

可以解决sql注入问题

PreparedStatement性能高于Statement(PreparedStatement有个预编译的效果)public classLoginServiceNew {publicUser findUserByNameAndPassword(String name, String password) {

User u= null;

String sql= "select id,name,password,email,birthday from t_user where name=? and password=?";

System.out.println(sql);//获取连接Connection

try (Connection conn =DBUtil.getConnection();

stmt=conn.prepareStatement(sql)) {

stmt.setString(1, name); //相当于对第一个 "?"赋值

stmt.setString(2, password); //相当于对第二个 "?"赋值

try (//执行sql语句，并返回结果

ResultSet rs =stmt.executeQuery()) {//处理结果

while(rs.next()) {

u= newUser();

u.setId(rs.getInt("id"));

u.setName(rs.getString("name"));

u.setPassword(rs.getString("password"));

u.setEmail(rs.getString("email"));

u.setBirthday(rs.getDate("birthday"));

}

}

}catch(SQLException e) {

e.printStackTrace();

}returnu;

}

}

# 利用上方的方法修改插入数据

@Testpublic voidtestInsert() {

String sql= "insert into t_user(name,password,email,birthday) values (?,?,?,?)";try(

Connection conn=DBUtil.getConnection();

PreparedStatement stmt=conn.prepareStatement(sql);

) {

stmt.setString(1, "dog");

stmt.setString(2, "123456");

stmt.setString(3, "dog@163.com");//stmt.setDate(4, new java.sql.Date(new Date().getTime()));

stmt.setString(4, "1997-07-01");int flag =stmt.executeUpdate();if (flag > 0) {

System.out.println("成功");

}else{

System.out.println("插入失败");

}

}catch(SQLException e) {

e.printStackTrace();

}

}