2019-03-28 JDBC
public class JDBCDemo1 {
public static void main(String[] args) {
//1.加载数据库驱动程序（需要把驱动加载到方法区）
Connection conn =null;
PreparedStatement ps = null;
ResultSet rs =null;
try {
//1.加载数据库驱动程序（需要把驱动加载到方法区） 可以省略
// Class.forName("com.mysql.jdbc.Driver");
// 2.利用驱动管理器获取数据库连接
// localhost 本地：如果需要连接他人数据库 修改成对方ip
// 3306 端口号 是mysql默认端口号
// jdbc 数据库名称（不是连接名称）
// useUnicode=true&characterEncoding=utf8 支持中文
String url = "jdbc:mysql://localhost:3306/javademo?useUnicode=true&characterEncoding=utf8";
String user = "root";
String password = "root";
conn = DriverManager.getConnection(url,user,password);
//3.获取SQL语句对象
String sql = "select * from dept";
ps = conn.prepareStatement(sql);
//4.执行语句
rs = ps.executeQuery();
while(rs.next()) {
//获取编号
int deptno = rs.getInt("deptno");
//获取部门名称
String dname = rs.getString("dname");
//获取工作地点
String loc = rs.getString("loc");
System.out.println(deptno+","+dname+","+loc);
}
} catch (SQLException e) {
e.printStackTrace();
}
finally {
if(conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
if(ps != null) {
try {
ps.close();
} catch (SQLException e) {
e.printStackTrace();
}
if(rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
}
}
}
public class JDBCDemo2 {
/**
* 查询emp表中的empno,ename,sal,hiredate在控制台显示
* @param args
*/
public static void main(String[] args) {
Connection conn = null;
PreparedStatement ps =null;
ResultSet rs =null;
try {
String url = "jdbc:mysql://localhost:3306/javademo?useUnicode=true&characterEncoding=utf8";
String user = "root";
String password = "root";
conn = DriverManager.getConnection(url, user, password);
String sql = "select * from emp";
ps = conn.prepareStatement(sql);
rs = ps.executeQuery();
while(rs.next()) {
int empno = rs.getInt("empno");
String ename = rs.getString("ename");
double sal = rs.getDouble("sal");
String hiredate = rs.getString("hiredate");
System.out.println(empno+","+ename+","+sal+","+hiredate);
}
} catch (SQLException e) {
e.printStackTrace();
}
finally {
if(conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(ps != null) {
try {
ps.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
}
/**
* 查询emp表中的empno,ename,sal,hiredate在控制台显示
* @param args
*/
public static void main(String[] args) {
Scanner scan = new Scanner(System.in);
System.out.println("请输入要查询的部门代码：");
int dnum = scan.nextInt();
Connection conn = null;
PreparedStatement ps =null;
ResultSet rs =null;
try {
String url = "jdbc:mysql://localhost:3306/javademo?useUnicode=true&characterEncoding=utf8";
String user = "root";
String password = "root";
conn = DriverManager.getConnection(url, user, password);
String sql = "select * from emp where deptno =?";
ps = conn.prepareStatement(sql);
ps.setInt(1,dnum);
rs = ps.executeQuery();
while(rs.next()) {
int empno = rs.getInt("empno");
String ename = rs.getString("ename");
double sal = rs.getDouble("sal");
String hiredate = rs.getString("hiredate");
System.out.println(empno+","+ename+","+sal+","+hiredate);
}
} catch (SQLException e) {
e.printStackTrace();
}
finally {
if(conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(ps != null) {
try {
ps.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
}
/**
* 查询emp表中姓名包含S的员工姓名，工资
* @param args
*/
public static void main(String[] args) {
Connection conn = null;
PreparedStatement ps =null;
ResultSet rs =null;
try {
String url = "jdbc:mysql://localhost:3306/javademo?useUnicode=true&characterEncoding=utf8";
String user = "root";
String password = "root";
conn = DriverManager.getConnection(url, user, password);
String sql = "select ename,sal from emp where ename like '%S%' ";
ps = conn.prepareStatement(sql);
rs = ps.executeQuery();
while(rs.next()) {
String ename = rs.getString("ename");
double sal = rs.getDouble("sal");
System.out.println(ename+","+sal);
}
} catch (SQLException e) {
e.printStackTrace();
}
finally {
if(conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(ps != null) {
try {
ps.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
}
