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());
}
}