package com.Jdbc.demo;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.Statement;

import com.mysql.jdbc.Connection;

public class jdbc02 {

public static final String url = "jdbc:mysql://localhost:3306/school?useUnicode=true&characterEncoding=UTF-8"; //连接数据库的URL地址

public static String username = "root"; //数据库的用户名

public static String password = "";  //数据库的密码

public static Connection conn=null;//连接对象

    public static Statement stmt=null;//语句

    public static ResultSet rs = null;//结果集

//1.加载MySQL数据库驱动

    static

    {

try {

Class.forName("com.mysql.jdbc.Driver");

//2、建立数据库连接

conn = (Connection) DriverManager.getConnection(url,username,password);

if(conn != null)

{

System.out.println("数据库连接正常");

}

else

{

System.out.println("数据库连接失败");

}

} 

catch (Exception e)

{

e.printStackTrace();

}

  }

  //查询所有的学生资料

  public static void query()

  {

  String sql = "select * from students;";

  try {

  stmt = conn.createStatement();

  rs = stmt.executeQuery(sql);

  while(rs.next())

  {

  System.out.println("学号："+rs.getInt("sid")+",姓名："+rs.getString("sname")+",年龄:"+rs.getInt("age")+",性别："+rs.getString("gender"));

  }

} catch (Exception e)

  {

e.printStackTrace();

  }

  finally 

  {

destoryResource(); 

}

  }

//添加学生方法

public static boolean add()

{

String sql = "insert into Students values (11,'张三天',138,'f','zhangsan@qq.com','广州阳江');";

try

{

stmt = conn.createStatement();

int result = stmt.executeUpdate(sql);

if(result > 0)

{

System.out.println("数据添加成功");

return true;

}

else

{

System.out.println("数据库添加失败");

return false;

}

}

catch(Exception ex)

{

ex.printStackTrace();

return false;

}

finally

{

destoryResource();

}

}

  //释放资源的方法

  public static void destoryResource()

  {

  try {

  if(rs != null)

  {

  rs.close();

  rs = null;

  }

  if (stmt != null)

  {

  stmt.close();

  stmt = null;

  }

} catch (Exception e) {

// TODO: handle exception

e.printStackTrace();

}

  }

  //释放最后资源

  public static void destoryallResource()

  {

  try 

  {

  if (conn != null)

  {

conn.close();

conn = null;

}

} catch (Exception e) {

// TODO: handle exception

e.printStackTrace();

}

  }

  //删除指定学号的学生资料

  public static boolean delete(int sid)

  {

  String sql = "delete from students where sid="+sid;

try

{

stmt = conn.createStatement();

int result = stmt.executeUpdate(sql);

if(result>0)

{

System.out.println("数据添删除成功");

return true;

}

else

{

System.out.println("数据添没有删除");

return false;

}

}

catch(Exception ex)

{

ex.printStackTrace();

return false;

}

finally

{

destoryResource();

}

  }

  //修改所有学生的年龄为20岁

  public static boolean update(int age)

  {

  String sql = "update students set age="+age;

  try

  {

  stmt = conn.createStatement();

  int result = stmt.executeUpdate(sql);

  if(result>0)

  {

  return true;

  }

  else

  {

  return false;

  }

  }

  catch(Exception ex)

  {

  ex.printStackTrace();

  return false;

  }

  finally

  {

  destoryResource();

  }

  }

  public static void main(String[] args) 

  {

  jdbc02.query();  //查询语句

  if (jdbc02.add()) 

  {

  System.out.println("添加成功！");

}

  else 

  {

  System.out.println("添加失败！");

}

  System.out.println("---------------------");

  jdbc02.query();

  jdbc02.delete(11);

System.out.println("------删除学号为11的学生之后--------");

jdbc02.query();

jdbc02.update(20);

System.out.println("------修改所有学生年龄为20岁--------");

jdbc02.query();

jdbc02.destoryallResource();  //释放资源

  }

}