Java,mysql数据库连接
数据库语句:
create database school;
use school;
create table student(
Sno char(6),
Sname varchar(8),
Ssex char(2),
Sage smallint,
Sdept varchar(15)
)
create table course(
Cno char(4),
Cname varchar(20),
Cpno char(4),
Ccredit tinyint
)
create table SC(
Sno char(4),
Cno char(4),
Grade int
)
insert into student values("4001","赵伟","男",20,"SX");
insert into student values("4002","杨华","女",21,"JSJ");
insert into student values("4003","王华","男",20,"ASD");
select * from student where Ssex = '男'
create table user(
\`id\` int primary key auto_increment,
\`name\` varchar(40),
\`password\` varchar(40),
\`email\` varchar(60),
\`birthday\` date
);
insert into user(\`name\`,\`password\`,\`email\`,\`birthday\`) values("zs","123456","zs@sina.com","1980-12-04");
insert into user (\`name\`,\`password\`,\`email\`,\`birthday\`) values("lisi","123456","lisi@sina.com","1981-12-04");
insert into user(\`name\`,\`password\`,\`email\`,\`birthday\`) values("wangwu","123456","wangwu@sina.com","1979-12-04");
Java代码使用statement:
//记得导入mysql的jar包
package test;
import java.io.UnsupportedEncodingException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class Test {
public static void main(String\[\] args) throws UnsupportedEncodingException {
Statement stmt=null;
Connection con = null;
String sql = null;
ResultSet rs = null;
try {
//装载驱动
Class.forName("com.mysql.jdbc.Driver");
//连接数据库时,注意连接时候的字符编码
String url="jdbc:mysql://localhost:3306/school?useUnicode=true&characterEncoding=utf8";
String user = "root";
String pwd = "123456";
//建立连接
con = DriverManager.getConnection(url,user,pwd);
//sql = "select * from student where Ssex = '男'";
//准备sql语句 ,使用statuement,最原始,最不安全,最不方便的方法
// stmt = con.createStatement();
//创建语句,接收结果
// rs = stmt.executeQuery(sql);
// System.out.println("Sno\\tSname\\tSsex\\tSage\\tSdept");
// while(rs.next()){
// System.out.println(rs.getString("Sno")+"\\t"+
// rs.getString("Sname")+"\\t"+
// rs.getString("Ssex")+"\\t"+
// rs.getInt("Sage")+"\\t"+
// rs.getString("Sdept"));
// }
//1. 更新语句
// sql = "update student set Sage = 21 where Sno = '4003'";
// stmt = con.createStatement();
// int i = stmt.executeUpdate(sql);
// System.out.println("执行条数:"+i);
//2.删除语句
// sql = "delete from student where Sno = '4002'";
// stmt = con.createStatement();
// int i = stmt.executeUpdate(sql);
// System.out.println("执行条数:"+i);
//3.查询
sql = "select * from user ";
stmt = con.createStatement();
rs = stmt.executeQuery(sql);
System.out.println("id\\tname\\tpassword\\temail\\tbirthday");
while(rs.next()){
System.out.println(rs.getString("id")+"\\t"+
rs.getString("name")+"\\t"+
rs.getString("password")+"\\t"+
rs.getString("email")+"\\t"+
rs.getString("birthday"));
}
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
if(stmt!=null){
try {
stmt.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
stmt = null;
}
if(con!=null){
try {
con.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
con = null;
}
if(rs!=null){
try {
rs.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
rs = null;
}
}
}
}