下载相关jar包
在连接数据库之前我们需要下载一个jar包,这个jar包是用来提供mysql驱动的,这里我给出这个jar包下载链接:支持JDBC驱动jar包
IDE中引用jar包
IDE(集成化开发环境)
java语言常见的IDE有 eclipse、IDEA
我们就以这两个为例
eclipse:
点击项目文件夹->右键->新建文件夹(命名为lib好了)
如图所示:
然后把jar包复制到文件夹中
右键->添加到构建路径
然后就大功告成
IDEA
点击工具栏中 file->Project Structure
然后进入这个界面
点击右边的+号选择jar文件所在路径
添加成功后,我们就可以点击右下角的ok按钮
建议大家学习java时候使用IDEA进行学习,eclipse现在用的比较少了。
IDEA逐渐成为主流IDE
这里我给出IDEA下载地址:IDEA下载地址
连接数据库代码
在连接数据库之前我们先要有一个数据库
这里我给大家一个数据库
也就是下面这些SQL语句代码了,大家把它粘贴到你的SQLyog或者Navicat上运行下就有这个数据库了
下面我们都根据这个数据库进行练习
/*
SQLyog Ultimate v11.24 (32 bit)
MySQL - 5.6.39 : Database - af_school
*********************************************************************
*/
/*!40101 SET NAMES utf8 */;
/*!40101 SET SQL_MODE=''*/;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/`af_school` /*!40100 DEFAULT CHARACTER SET utf8 */;
USE `af_school`;
/*Table structure for table `exam` */
DROP TABLE IF EXISTS `exam`;
CREATE TABLE `exam` (
`id` int(11) NOT NULL COMMENT '学号',
`chinese` int(11) DEFAULT NULL COMMENT '语文成绩',
`english` int(11) DEFAULT NULL COMMENT '英语成绩',
`math` int(11) DEFAULT NULL COMMENT '数学成线',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*Data for the table `exam` */
insert into `exam`(`id`,`chinese`,`english`,`math`) values (20180001,89,90,98),(20180002,78,82,93),(20180003,90,73,95),(20180004,88,98,83),(20180005,96,79,75),(20180006,77,98,82);
/*Table structure for table `leave_event` */
DROP TABLE IF EXISTS `leave_event`;
CREATE TABLE `leave_event` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '请假记录ID',
`stuId` int(11) NOT NULL COMMENT '学生ID',
`daysFrom` date DEFAULT NULL COMMENT '哪天开始',
`daysTo` date DEFAULT NULL COMMENT '哪天结束',
`type` tinyint(4) DEFAULT NULL COMMENT '类型,0病假,1事假',
`reason` varchar(256) DEFAULT NULL COMMENT '事由',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;
/*Data for the table `leave_event` */
insert into `leave_event`(`id`,`stuId`,`daysFrom`,`daysTo`,`type`,`reason`) values (1,20180001,'2018-01-02','2018-01-03',0,'感冒'),(2,20180001,'2018-03-06','2018-03-08',0,'发烧'),(3,20180003,'2018-03-01','2018-03-07',1,'出国旅游'),(4,20180005,'2018-03-07','2018-03-07',1,'家里有事'),(5,20180003,'2018-03-17','2018-03-18',0,'不舒服'),(6,20180004,'2018-03-24','2018-03-30',2,'出国'),(7,20180004,'2018-03-24','2018-03-30',2,'出国'),(8,20180004,'2018-03-24','2018-03-30',2,'出国');
/*Table structure for table `student` */
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
`id` int(11) NOT NULL COMMENT '学号',
`name` varchar(32) NOT NULL COMMENT '姓名',
`sex` tinyint(1) DEFAULT NULL COMMENT '性别',
`phone` varchar(16) DEFAULT '13800000000' COMMENT '手机号',
`birthday` date DEFAULT NULL COMMENT '生日',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*Data for the table `student` */
insert into `student`(`id`,`name`,`sex`,`phone`,`birthday`) values (20180001,'盖聂',1,'1409900089','1982-03-09'),(20180002,'卫庄',1,'1282399999','1993-10-01'),(20180003,'张良',1,NULL,'1996-03-11'),(20180004,'伏念',1,NULL,'1982-09-24'),(20180005,'颜路',1,'13699292899','1983-11-21'),(20180006,'赤练',0,'13819289890','1998-03-12'),(20180007,'端木蓉',0,'13800000000','1978-05-12'),(20180008,'盗跖',1,'13410012908','1993-09-10'),(20180009,'白凤',1,'13509890090','1994-04-20'),(20180010,'天明',1,'18799891829','2002-04-19'),(20180011,'月儿',0,'13882938990','2003-06-10');
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
测试连接数据库(不执行SQL语句)
package com.company;
import java.sql.Connection;
import java.sql.DriverManager;
public class Main {
public static void testConnect() throws Exception
{
// 注册MySQL驱动 (可以省略这一步)
Class.forName("com.mysql.jdbc.Driver");
// 连接MySQL服务器
String username= "root";
String password = "";
String connectionUrl = "jdbc:mysql://127.0.0.1:3306/af_school?useUnicode=true&characterEncoding=UTF-8";
Connection conn = DriverManager.getConnection(connectionUrl, username, password);
System.out.println("连接成功!");
conn.close();
System.out.println("关闭连接!");
}
public static void main(String[] args) throws Exception {
testConnect();
}
}
连接数据库并执行查询语句
package com.company;
import java.sql.*;
import java.util.Date;
public class Main2 {
public static void linktest() throws Exception{
String username= "root";
String password = "";
String connectionUrl = "jdbc:mysql://127.0.0.1:3306/af_school?useUnicode=true&characterEncoding=UTF-8";
Connection conn = DriverManager.getConnection(connectionUrl,username,password);
Statement state=conn.createStatement();
ResultSet resultSet=state.executeQuery("select * from student");
System.out.println("链接成功");
System.out.println("学号 \t"+"姓名 \t"+"性别 \t"+"电话 \t"+"生日 \t");
while (resultSet.next()){
String stuID=resultSet.getString("id");
String stuName=resultSet.getString("name");
int sex=resultSet.getInt("sex");
String phoneNumber=resultSet.getString("phone");
Date date=resultSet.getDate("birthday");
System.out.println(stuID+"\t"+stuName+"\t "+sex+"\t"+phoneNumber+"\t"+date);
}
System.out.println("查询结束");
conn.close();
System.out.println("关闭链接");
}
public static void main(String[] args) throws SQLException {
try {
linktest();
} catch (Exception e) {
e.printStackTrace();
}
}
}
连接数据库并执行插入语句
package com.company;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
public class Main3 {
public static void link_JDBC() throws Exception {
Class.forName("com.mysql.jdbc.Driver");
String username="root";
String password="";
String conURL="jdbc:mysql://127.0.0.1:3306/af_school?" +
"useUnicode=true&characterEncodeing=UTF-8";
Connection con= DriverManager.getConnection(conURL,username,password);
System.out.println("连接成功!");
Statement statement=con.createStatement();
statement.execute("insert into student(id,name,sex,phone) values ('20180012','程麒阁',1,'13252702178')");
int count=statement.getUpdateCount();
System.out.println("受影响行数为:"+count);
con.close();
System.out.println("关闭连接!");
}
public static void main(String[] args) {
try {
link_JDBC();
} catch (Exception e) {
e.printStackTrace();
}
}
}
连接数据库并执行删除语句
package com.company;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
public class Main3 {
public static void link_JDBC() throws Exception {
Class.forName("com.mysql.jdbc.Driver");
String username="root";
String password="";
String conURL="jdbc:mysql://127.0.0.1:3306/af_school?" +
"useUnicode=true&characterEncodeing=UTF-8";
Connection con= DriverManager.getConnection(conURL,username,password);
System.out.println("连接成功!");
Statement statement=con.createStatement();
statement.execute("delete from student where id='20180011'");
int count=statement.getUpdateCount();
System.out.println("受影响行数为:"+count);
con.close();
System.out.println("关闭连接!");
}
public static void main(String[] args) {
try {
link_JDBC();
} catch (Exception e) {
e.printStackTrace();
}
}
}
连接数据库并修改数据
package com.company;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
public class Main3 {
public static void link_JDBC() throws Exception {
Class.forName("com.mysql.jdbc.Driver");
String username="root";
String password="";
String conURL="jdbc:mysql://127.0.0.1:3306/af_school?" +
"useUnicode=true&characterEncodeing=UTF-8";
Connection con= DriverManager.getConnection(conURL,username,password);
System.out.println("连接成功!");
Statement statement=con.createStatement();
statement.execute("update student set name='小白' where id='20180012'");
int count=statement.getUpdateCount();
System.out.println("受影响行数为:"+count);
con.close();
System.out.println("关闭连接!");
}
public static void main(String[] args) {
try {
link_JDBC();
} catch (Exception e) {
e.printStackTrace();
}
}
}