package com.wj.demo01;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import org.junit.Test;
public class Demo02 {
@Test
public void query() {
Connection conn=null;
Statement stmt=null;
ResultSet rs=null;
try {
Class.forName("com.mysql.jdbc.Driver");
String url="jdbc:mysql://localhost:3306/mydb3";
String user="root";
String password="1234";
conn=DriverManager.getConnection(url, user, password);
stmt=conn.createStatement();
rs=stmt.executeQuery("select * from student");
while(rs.next()) {
int id=rs.getInt("id");
String name=rs.getString("name");
String city=rs.getString("city");
int age=rs.getInt("age");
System.out.println(id+":"+name+":"+city+":"+age);
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}finally {
try {
if(null!=rs) {
rs.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
try {
if(null!=stmt) {
stmt.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
try {
if(null!=conn) {
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
@Test
public void delete() {
Connection conn=null;
Statement stmt=null;
try {
Class.forName("com.mysql.jdbc.Driver");
conn=DriverManager.getConnection("jdbc:mysql://localhost:3306/mydb3","root", "1234");
stmt=conn.createStatement();
String sql="delete from student where id=11";
int count=stmt.executeUpdate(sql);
System.out.println("删除的行数是"+count);
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}finally {
try {
if(null!=stmt) {
stmt.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
try {
if(conn!=null) {
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
@Test
public void update() {
Connection conn=null;
Statement stmt=null;
try {
Class.forName("com.mysql.jdbc.Driver");
String url="jdbc:mysql://localhost:3306/mydb3";
String user="root";
String password="1234";
conn=DriverManager.getConnection(url, user, password);
stmt=conn.createStatement();
String sql="update student set name='开心',city='广州' where id=11 ";
int count=stmt.executeUpdate(sql);
System.out.println("受影响的行数"+count);
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}finally {
try {
if(stmt!=null) {
stmt.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
try {
if(conn!=null) {
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
@Test
public void insert() {
Connection conn=null;
Statement stmt=null;
try {
Class.forName("com.mysql.jdbc.Driver");
String url="jdbc:mysql://localhost:3306/mydb3";
String user="root";
String password="1234";
conn=DriverManager.getConnection(url,user,password);
stmt=conn.createStatement();
String sql="insert into student values(null,'开心','广州',18)";
int count=stmt.executeUpdate(sql);
System.out.println("受影响的行数是"+count);
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}finally {
try {
if(stmt!=null) {
stmt.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
try {
if(conn!=null) {
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}