首先需要将mysql的驱动包导入java项目:
新建名为lib的folder,拖入驱动jar包
然后buildpath进项目
接下来开始写代码
1.加载注册驱动
2.获取连接对象
package jdbc_connectionDemo;
import java.sql.Connection;
//注意这里导包是用java中的sql包的接口而不是mysql包的实现类
import java.sql.DriverManager;
import org.junit.Test;
//获取数据库连接对象
public class ConnectionTest {
@Test
public void test1() throws Exception {
//1.加载注册驱动
Class.forName("com.mysql.jdbc.Driver");
//2.获取连接对象
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbcdemo", "root", "admin");//此时参数为数据库的url,账号,密码
}
}
Class.forName("");方法 的作用是 要求JVM查找并加载指定的类,在数据库使用中用来加载数据库的驱动包
在开发中这个方法常用于工厂设计模式创建对象
从jdk6开始可以不用手动加载注册(不推荐,且JavaWeb中不可用)
创表操作
package jdbc_connectionDemo;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
import org.junit.Test;
//获取数据库连接对象
public class ConnectionTest {
@Test
public void test1() throws Exception {
//1.加载注册驱动
Class.forName("com.mysql.jdbc.Driver");
//2.获取连接对象
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbcdemo", "root", "admin");//此时参数为数据库的url,账号,密码
//3.创建语句对象
Statement st = conn.createStatement();
//4.执行sql语句
st.executeUpdate(
"CREATE TABLE `t_student` (\r\n" +
" `id` bigint(20) NOT NULL,\r\n" +
" `name` varchar(255) DEFAULT NULL,\r\n" +
" `age` int(11) DEFAULT NULL,\r\n" +
" PRIMARY KEY (`id`)\r\n" +
") ENGINE=InnoDB DEFAULT CHARSET=utf8;");
//5.释放资源(后开先关)
st.close();
conn.close();
}
}
结果如下
接下来进行增删改操作
public class DMLTest {
@Test
//增加表内列
public void testInsert() throws Exception {
String sql = "INSERT INTO t_student (name,age) VALUES ('小万',21)";
//1.加载注册驱动
Class.forName("com.mysql.jdbc.Driver");
//2.获取连接对象
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbcdemo","root","admin");
//3.创建语句对象
Statement st = conn.createStatement();
//4.执行sql操作
st.executeUpdate(sql);
//5.释放资源
st.close();
conn.close();
}
@Test
//更新列
public void testUpdate() throws Exception {
String sql = "UPDATE t_student SET name = '小童',age = 20 WHERE id = 1";
//1.加载注册驱动
Class.forName("com.mysql.jdbc.Driver");
//2.获取连接对象
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbcdemo","root","admin");
//3.创建语句对象
Statement st = conn.createStatement();
//4.执行sql操作
st.executeUpdate(sql);
//5.释放资源
st.close();
conn.close();
}
@Test
//删除列
public void testDelete() throws Exception {
String sql = "Delete FROM t_student WHERE id = 1";
//1.加载注册驱动
Class.forName("com.mysql.jdbc.Driver");
//2.获取连接对象
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbcdemo","root","admin");
//3.创建语句对象
Statement st = conn.createStatement();
//4.执行sql操作
st.executeUpdate(sql);
//5.释放资源
st.close();
conn.close();
}
}
查询操作较为不同:
package jdbc_DQL;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import org.junit.Test;
public class DQLTest {
@Test
public void testQuery() throws Exception {
String sql = "SELECT * FROM t_student";
Class.forName("com.mysql.jdbc.Driver");
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbcdemo", "root", "admin");
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:"+id+",学生名字:"+name+",学生年龄:"+age);
}
rs.close();
st.close();
conn.close();
}
}
查询相对麻烦的步骤在于对表查询后信息的处理
用executeQuery返回的是一个查询结果集,用ResultSet来接收
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:"+id+",学生名字:"+name+",学生年龄:"+age);
}
