JDBC连接mysql
数据库
1、创建Java web项目
2、创建实体类Student.java
public class Student {
private int stu_id;
private String stu_name;
private int age;
private String gender;
private String phone;
@Override
public String toString() {
return "Student{" +
"stu_id=" + stu_id +
", stu_name='" + stu_name + '\'' +
", age=" + age +
", gender='" + gender + '\'' +
", phone='" + phone + '\'' +
'}';
}
//省略getter/setter方法
}
3、创建dao层PstDao.java
public class PstDao {
private static String driver= Prop.getP("driver");
private static String url=Prop.getP("url");
private static String user=Prop.getP("user");
private static String pwd=Prop.getP("pwd");
//获取连接
public static Connection getConn(){
try {
Class.forName(driver);
return DriverManager.getConnection(url,user,pwd);
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
//关闭资源
public static void close(Connection conn, PreparedStatement pst,ResultSet rs){
if (conn!=null){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (pst!=null){
try {
pst.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (rs!=null){
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
//查询
public static ResultSet query(String sql,Object ...params){
Connection conn = getConn();
PreparedStatement pst=null;
ResultSet rs=null;
try {
pst=conn.prepareStatement(sql);
for (int i = 0; i < params.length; i++) {
pst.setObject(i+1,params[i]);
}
rs=pst.executeQuery();
} catch (SQLException e) {
e.printStackTrace();
}
return rs;
}
//修改数据
public static int update(String sql,Object... params){
Connection conn=getConn();
PreparedStatement pst=null;
try {
pst = getConn().prepareStatement(sql);
for (int i = 0; i < params.length; i++) {
pst.setObject(i+1,params[i]);
}
return pst.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}finally {
try {
if (pst!=null){
pst.clearParameters();
}
} catch (SQLException e) {
e.printStackTrace();
}
try {
if (conn!=null){
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
return -1;
}
}
4、创建工具类Prop.java
用于读取配置文件,获取数据库连接
public class Prop {
private static Properties p=new Properties();
public static String getP(String param){
try {
p.load(Prop.class.getClassLoader().getResourceAsStream("db.properties"));
} catch (IOException e) {
e.printStackTrace();
}
return p.getProperty(param);
}
}
5、创建配置文件resources
文件夹
在该文件夹中添加db.properties
文件;
储存mysql
连接信息;
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://192.168.8.10:3306/MyStudent
user=root
pwd=ok
6、创建测试类Ctrl+Shift+t
public class PstDaoTest {
@Test
public void query() throws SQLException {
String sql="select * from student";
ResultSet rs=PstDao.query(sql);
while (rs.next()){
System.out.print(rs.getInt("stu_id") + "---");
System.out.print(rs.getString("Stu_name") + "---");
System.out.print(rs.getInt("age") + "---");
System.out.println(rs.getString("gender"));
}
rs.close();
}
@Test
public void update(){
String sql="update student set age=? where stu_name=?";
int num=PstDao.update(sql,18,"王晓红");
System.out.println(num>0?"更新成功":"更新失败");
}
}
7、测试查询方法,结果与mysql
中查询结果一致!
BaseDao操作方法(不便修改连接)
1、定义常用方法
获取连接、查询、插入等方法
public class BaseDao {
public Connection getConn() throws Exception {
//加载驱动
Class.forName("com.mysql.jdbc.Driver");
//驱动管理器来获取连接
String url="jdbc:mysql://192.168.8.10:3306/MyStudent";
Connection conn= DriverManager.getConnection(url,"root","ok");
return conn;
}
//查看数据库中的表
public void showTables() throws Exception {
//获取连接
Connection conn=getConn();
//获取Statement对象
Statement stmt=conn.createStatement();
String sql="show tables";//定义sql语句
ResultSet rs=stmt.executeQuery(sql);//获取查询语句结果集
while (rs.next()){//对结果遍历输出
System.out.println(rs.getString(1));
}
}
//查询方法
public ResultSet query(String sql,int id,String name) throws Exception{
//获取连接
Connection conn=getConn();
//获取Statement对象
// Statement stmt=conn.createStatement();
// ResultSet rs=stmt.executeQuery(sql);
PreparedStatement pst=conn.prepareStatement(sql);
pst.setObject(1,id);
pst.setObject(2,name);
ResultSet rs = pst.executeQuery();
return rs;
}
//插入方法
public int update(String sql) throws Exception{
//获取连接
Connection conn=getConn();
//获取Statement对象
Statement stmt=conn.createStatement();
int num = stmt.executeUpdate(sql);
return num;
}
public void close(Connection conn,Statement stmt,ResultSet rs) throws Exception{
if (conn!=null){
conn.close();
}
if (stmt!=null){
stmt.close();
}
if (rs!=null){
rs.close();
}
}
}
2、测试类
查询测试、插入测试、创建数据库、创建表等测试
public class BaseDaoTest {
@Test
public void showTables() throws Exception {
BaseDao dao=new BaseDao();
dao.showTables();
}
//查询数据
@Test
public void query() throws Exception {
BaseDao dao=new BaseDao();
String sql="select * from student where stu_id=? or stu_name like ?";
ResultSet rs=dao.query(sql,1,"王%");
ArrayList<Student> stuList=new ArrayList<>();
while (rs.next()){
Student s=new Student();
s.setStu_id(rs.getInt("stu_id"));
s.setStu_name(rs.getString("stu_name"));
s.setAge(rs.getInt("age"));
s.setGender(rs.getString("gender"));
s.setPhone(rs.getString( "phone"));
stuList.add(s);
}
for (Student student : stuList) {
System.out.println(student);
}
dao.close(null,null,rs);
}
//插入数据
@Test
public void update() throws Exception {
BaseDao dao=new BaseDao();
String sql="insert into student(stu_id,stu_name,age,gender,phone) values (4,'小刘',23,'男','17623751689'),(5,'刘哈儿',23,'男','17623751659')";
int num=dao.update(sql);
System.out.println(num>0?"插入成功":"插入失败");
}
//创建数据库,切换数据库,创建表
@Test
public void testDo() throws Exception{
BaseDao dao=new BaseDao();
Connection conn=dao.getConn();
Statement stmt=conn.createStatement();
String createDatabase="create database if not exists userControl";
stmt.execute(createDatabase);//创建数据库
String changeDatabase="use MyStudent";
stmt.execute(changeDatabase);
String createTable="create table if not exists user_info(uid int(11) auto_increment primary key,uname varchar(30),password varchar(30))";
stmt.execute(createTable);
dao.close(conn,stmt,null);
}
}
该方法效果与上面相同,但不便于修改连接;