JDBC操作

最新推荐文章于 2022-03-18 16:56:45 发布

zhouzhou_98

最新推荐文章于 2022-03-18 16:56:45 发布

阅读量155

点赞数

本文链接：https://blog.csdn.net/zhouzhou_98/article/details/81587599

版权

1、JDBC概述

2、获取数据库连接

JDBC操作必须得有加载驱动包的安装才可以运行

加载驱动包的下载地址： http://dev.mysql.com/downloads/connector/j/5.1.html

3、创建表以及处理异常操作

package com_jdbc01;

步骤1: 加载注册驱动.

Class.forName(“com.mysql.jdbc.Driver”);

为什么说这行代码就在完成加载注册驱动的操作.

1):把com.mysql.jdbc.Driver这份字节码文件加载进JVM.

2):把字节码加载进JVM之后,就会立刻执行该类的静态代码块.

步骤2: 获取连接对象,通过DriverManager的静态方法(getConnection).

Connection conn = DriverManager.getConnection (String url,String username,String password);

参数:

url : jdbc:mysql://数据库服务器安装电脑的主机IP:端口/哪一个数据库名称

连接本机: jdbc:mysql://localhost:3306/jdbcdemo

如果连接的数据库服务器在本机,并且端口是3306,则可以简写:

jdbc:mysql:///jdbcdemo

username: 所连接数据库服务器的用户账号(root)

password: 所连接数据库服务器的用户密码(123456)

验证已经获取连接:可以在MySQL控制台,使用命令:show processlist; 查看MySQL运行进程.

* */

import static org.junit.Assert.*;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.SQLException;

import java.sql.Statement;

import org.junit.Test;

public class ConnectionDemo {

@Test

public void test1() {

/* 步骤1: 加载注册驱动.*/

String sql="CREATE TABLE t_student(id BIGINT PRIMARY KEY AUTO_INCREMENT,name VARCHAR(20) NOT NULL,age INT NOT NULL)";

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

/* 步骤2: 获取连接对象*/

Connection conn=null;

Statement st=null;

try {

conn=DriverManager.getConnection("jdbc:mysql://localhost:3306/productdemo", "root", "123456");

st=conn.createStatement();

int row=st.executeUpdate(sql);

if(row==0)

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

} catch (Exception e) {

// TODO: handle exception

e.printStackTrace();

}finally {

try {

if(st!=null)

st.close();

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

finally {

try {

if(conn!=null)

conn.close();

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

@Test

public void test2() throws Exception {

String sql="CREATE TABLE t_student(id BIGINT PRIMARY KEY AUTO_INCREMENT,name varchar(20),age int)";

Connection conn=DriverManager.getConnection("jdbc:mysql://localhost:3306/productdemo", "root", "123456");

Statement st=conn.createStatement();

int row=st.executeUpdate(sql);

if(row==0)

{

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

}

st.close();

conn.close();

}*/

@Test

public void testbyJAVA7(){

String sql="CREATE TABLE t_student(id BIGINT PRIMARY KEY AUTO_INCREMENT,name varchar(20),age int)";

try(

Connection conn=DriverManager.getConnection("jdbc:mysql://localhost:3306/productdemo", "root", "123456");

Statement st=conn.createStatement();

){

int row=st.executeUpdate(sql);

if(row==0)

{

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

}

catch (Exception e) {

// TODO: handle exception

e.printStackTrace();

}

4、DDL操作

package com_jdbc01;

import static org.junit.Assert.*;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.Statement;

import org.junit.Test;

public class DdlDemo {

@Test

public void testinsert() throws Exception {

String sql="INSERT INTO t_student(name,age) VALUES('洲洲',20)";

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

Connection conn=DriverManager.getConnection("jdbc:mysql://localhost:3306/productdemo", "root", "123456");

Statement st=conn.createStatement();

int row=st.executeUpdate(sql);

if(row!=0)

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

st.close();

conn.close();

}

@Test

public void testupdate() throws Exception {

String sql="UPDATE t_student SET name='洲洲'";

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

Connection conn=DriverManager.getConnection("jdbc:mysql://localhost:3306/productdemo", "root", "123456");

Statement st=conn.createStatement();

int row=st.executeUpdate(sql);

if(row!=0)

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

st.close();

conn.close();

}

@Test

public void testdelete() throws Exception {

String sql="DELETE FROM t_student where id=2 or id=3";

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

Connection conn=DriverManager.getConnection("jdbc:mysql://localhost:3306/productdemo", "root", "123456");

Statement st=conn.createStatement();

int row=st.executeUpdate(sql);

if(row!=0)

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

st.close();

conn.close();

}

4、DQL操作

package com_jdbc01;

import static org.junit.Assert.*;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.Statement;

import org.junit.Test;

public class DQLdemo {

@Test

public void testdql() throws Exception {

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

String sql="SELECT *FROM t_student";

Connection conn=DriverManager.getConnection("jdbc:mysql://localhost:3306/productdemo", "root", "123456");

Statement st=conn.createStatement();

ResultSet rs=st.executeQuery(sql);

while(rs.next())

{

long id=rs.getLong("id");

String name=rs.getString("name");

int age=rs.getInt("age");

System.out.println(id+" "+name+" "+age);

}

rs.close();

st.close();

conn.close();

}

5、DAO设计

为了简化代码，本次代码使用了lombok.jar包，就是所谓的小辣椒，减少类的setter、getter以及tostring（）方法的构造。 下载链接 点击可下载

操作步骤如下：

包与包之间的结构关系

具体代码如下：

1、学生对象类的设计

package jdbc.mis.domain;

import lombok.Data;

@Data

public class Student {

private long id; //数据库中的学生对象的id

private String name; //数据库中的学生对象的名字

private int age; //数据库中的学生对象年龄

}

2、接口及相关重载方法的设计

package jdbc.mis.dao.impl;

import java.util.List;

import jdbc.mis.domain.Student;

public interface StudentDAOimpl {

/**

* 保存用户对象

* @param stu

void add(Student stu);

/**

* 修改用户对象

* @param stu

void update(Student stu);

/**

* 删除用户对象

* @param id

void delete(long id);

/**

* 查询用户对象，并且返回一个对象

* @param stu

* @return

/**

* 查询用户对象，并且返回多个对象

* @return

List<Student> list();

Student getone(long id);

}

3、实现类的设计

package jdbc.mis.dao;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.Statement;

import java.util.ArrayList;

import java.util.List;

import jdbc.mis.dao.impl.StudentDAOimpl;

import jdbc.mis.domain.Student;

public class IStudentDAO implements StudentDAOimpl {@Override

/*插入语句*/

public void add(Student stu) {

// TODO Auto-generated method stub

String sql1="INSERT INTO t_student (name,age)VALUES(stu.getName(),stu.getAge())";

StringBuilder sql=new StringBuilder(100);

sql.append("INSERT INTO t_student (name,age)VALUES(");

sql.append("'").append(stu.getName()).append("'").append(",").append(stu.getAge()).append(")");

Connection conn=null;

Statement st=null;

try {

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

} catch (ClassNotFoundException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

try {

conn=DriverManager.getConnection("jdbc:mysql://localhost:3306/productdemo", "root", "123456");

st=conn.createStatement();

int row=st.executeUpdate(sql.toString());

if(row!=0)

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

} catch (Exception e) {

// TODO: handle exception

e.printStackTrace();

}finally {

try {

if(st!=null)

st.close();

} catch (Exception e2) {

// TODO: handle exception

e2.printStackTrace();

}

finally {

try {

if(conn!=null)

conn.close();

} catch (Exception e3) {

// TODO: handle exception

e3.printStackTrace();

}

/*更新语句*/

@Override

public void update(Student stu) {

// TODO Auto-generated method stub

String sql1="UPDATE t_student SET name=stu.getname,age=stu.getage where id='+stu.getid+'";

StringBuilder sql=new StringBuilder(100);

sql.append("UPDATE t_student SET name=").append(" ' ").append(stu.getName()).append(" ' ").append(" , ").append("age=").append(stu.getAge()).append(" where id=").append(stu.getId());

Connection conn=null;

Statement st=null;

try {

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

} catch (ClassNotFoundException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

try {

conn=DriverManager.getConnection("jdbc:mysql://localhost:3306/productdemo", "root", "123456");

st=conn.createStatement();

int row=st.executeUpdate(sql.toString());

if(row!=0)

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

} catch (Exception e) {

// TODO: handle exception

e.printStackTrace();

}finally {

try {

if(st!=null)

st.close();

} catch (Exception e2) {

// TODO: handle exception

e2.printStackTrace();

}

finally {

try {

if(conn!=null)

conn.close();

} catch (Exception e2) {

e2.printStackTrace();

}

/*删除语句*/

@Override

public void delete(long id) {

// TODO Auto-generated method stub

String sql="DELETE FROM t_student where id= "+ id;

Connection conn=null;

Statement st=null;

try {

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

} catch (ClassNotFoundException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

try {

conn=DriverManager.getConnection("jdbc:mysql://localhost:3306/productdemo", "root", "123456");

st=conn.createStatement();

int row=st.executeUpdate(sql);

if(row!=0)

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

} catch (Exception e) {

// TODO: handle exception

e.printStackTrace();

}finally {

try {

if(st!=null)

st.close();

} catch (Exception e2) {

// TODO: handle exception

e2.printStackTrace();

}

finally {

try {

if(conn!=null)

conn.close();

} catch (Exception e2) {

e2.printStackTrace();

}

/*获取单行查询语句*/

@Override

public Student getone(long id) {

// TODO Auto-generated method stub

String sql="SELECT *FROM t_student where id= "+id;

Connection conn=null;

Statement st=null;

ResultSet re=null;

try {

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

} catch (Exception e) {

// TODO: handle exception

e.printStackTrace();

}

try {

conn=DriverManager.getConnection("jdbc:mysql://localhost:3306/productdemo", "root", "123456");

st=conn.createStatement();

re=st.executeQuery(sql);

if(re.next())

{

Student stu=new Student();

stu.setAge(re.getInt("age"));

stu.setId(re.getLong("id"));

stu.setName(re.getString("name"));

return stu;

}

} catch (Exception e) {

// TODO: handle exception

e.printStackTrace();

}finally {

try {

if(re!=null)

re.close();

} catch (Exception e2) {

// TODO: handle exception

e2.printStackTrace();

}

finally {

try {

if(st!=null)

st.close();

} catch (Exception e3) {

// TODO: handle exception

e3.printStackTrace();

}finally {

try {

if(conn!=null)

conn.close();

} catch (Exception e4) {

// TODO: handle exception

e4.printStackTrace();

}

return null;

}

/*获取全部数据的查询语句*/

@Override

public List<Student> list() {

// TODO Auto-generated method stub

List<Student>list=new ArrayList<>();

String sql="SELECT *FROM t_student ";

Connection conn=null;

Statement st=null;

ResultSet re=null;

try {

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

} catch (Exception e) {

// TODO: handle exception

e.printStackTrace();

}

try {

conn=DriverManager.getConnection("jdbc:mysql://localhost:3306/productdemo", "root", "123456");

st=conn.createStatement();

re=st.executeQuery(sql);

while(re.next())

{

Student stu=new Student();

stu.setAge(re.getInt("age"));

stu.setId(re.getLong("id"));

stu.setName(re.getString("name"));

list.add(stu);

}

} catch (Exception e) {

// TODO: handle exception

e.printStackTrace();

}finally {

try {

if(re!=null)

re.close();

} catch (Exception e2) {

// TODO: handle exception

e2.printStackTrace();

}

finally {

try {

if(st!=null)

st.close();

} catch (Exception e3) {

// TODO: handle exception

e3.printStackTrace();

}finally {

try {

if(conn!=null)

conn.close();

} catch (Exception e4) {

// TODO: handle exception

e4.printStackTrace();

}

return list;

}

/*测试*/

package jdbc.mis.IStudentTest;

import static org.junit.Assert.*;

import java.util.ArrayList;

import java.util.List;

import org.junit.Test;

import jdbc.mis.dao.IStudentDAO;

import jdbc.mis.domain.*;

public class StudentTest {

@Test

public void addtest() throws Exception {

IStudentDAO stu=new IStudentDAO();

Student s=new Student();

s.setAge(20);

s.setName("周周");

stu.add(s);

}

@Test

public void updatetest() throws Exception {

IStudentDAO stu=new IStudentDAO();

Student s=new Student();

s.setId(5);

s.setAge(21);

s.setName("lulu");

stu.update(s);

}

@Test

public void deletetest() throws Exception {

IStudentDAO stu=new IStudentDAO();

stu.delete(10);

}

@Test

public void gettest() throws Exception {

IStudentDAO stu=new IStudentDAO();

System.out.println(stu.getone(11));

}

@Test

public void listtest() throws Exception {

IStudentDAO stu=new IStudentDAO();

System.out.println(stu.list());

}

zhouzhou_98

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
JDBC操作

1、JDBC概述 2、获取数据库连接 JDBC操作必须得有加载驱动包的安装才可以运行加载驱动包的下载地址： http://dev.mysql.com/downloads/connector/j/5.1.html 3、创建表以及处理异常操作package com_jdbc01;/* 步骤1: 加载注册...
复制链接

扫一扫