java增删改查算实习_复习java增删改查

package com.dal;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.text.SimpleDateFormat;

import java.util.*;

import com.data.*;

import com.model.*;

import com.mysql.jdbc.Connection;

public class newsDal {

static Connection con;

static java.sql.Statement sql;

static ResultSet res;

public int countNews()

{

int rowCount =0;

try

{

sql=mydata.conn().createStatement();

res=sql.executeQuery("select count(*) totalCount from j_new");

/*while(res.next())

{

rowCount=res.getInt("id ");

}*/

if(res.next()) {

rowCount=res.getInt("totalCount");

}

}

catch (SQLException e)

{

// TODO Auto-generated catch block

e.printStackTrace();

}

//res=sql.executeQuery("select count(*) from table");

return rowCount;

}

//新闻列表

public List getListNew()

{

List list=new ArrayList();

try

{

//sql=(Statement) myda.conn().createStatement();

sql=mydata.conn().createStatement();

res=sql.executeQuery("SELECT * FROM j_new order by dateAndTime desc, sort asc");

while(res.next())

{

newsModel model=new newsModel();

model.setId(Integer.parseInt(res.getString("id")));

model.setTitle(res.getString("title"));

model.setType(Integer.parseInt(res.getString("type")));

model.setContent(res.getString("content"));

model.setHints(Integer.parseInt(res.getString("hints")));

model.setSource(res.getString("source"));

model.setTime(res.getTimestamp("dateAndTime"));

model.setAuthor(res.getString("author"));

model.setSort(Integer.parseInt(res.getString("sort")));

list.add(model);

}

sql.close();

}

catch (Exception e)

{

e.printStackTrace();

}

return list;

}

//新闻分页列表

public List getPageNews(int startsize, int pagenum)

{

List list=new ArrayList();

try

{

//sql=(Statement) myda.conn().createStatement();

sql=mydata.conn().createStatement();

res=sql.executeQuery("SELECT * FROM j_new order by dateAndTime desc, sort asc LIMIT "+startsize+","+pagenum);

while(res.next())

{

newsModel model=new newsModel();

model.setId(Integer.parseInt(res.getString("id")));

model.setTitle(res.getString("title"));

model.setType(Integer.parseInt(res.getString("type")));

model.setContent(res.getString("content"));

model.setHints(Integer.parseInt(res.getString("hints")));

model.setSource(res.getString("source"));

model.setTime(res.getTimestamp("dateAndTime"));

model.setAuthor(res.getString("author"));

model.setSort(Integer.parseInt(res.getString("sort")));

list.add(model);

}

sql.close();

}

catch (Exception e)

{

e.printStackTrace();

}

return list;

}

//新闻详情

public newsModel getNewsModel(int id) throws SQLException

{

newsModel model=new newsModel();

sql=mydata.conn().createStatement();

res=sql.executeQuery("select * from j_new where id="+id);

try

{

if(res.next())

{

model.setId(Integer.parseInt(res.getString("id")));

model.setTitle(res.getString("title"));

model.setType(Integer.parseInt(res.getString("type")));

model.setContent(res.getString("content"));

model.setHints(Integer.parseInt(res.getString("hints")));

model.setSource(res.getString("source"));

model.setTime(res.getDate("dateAndTime"));

model.setAuthor(res.getString("author"));

model.setSort(Integer.parseInt(res.getString("sort")));

}

sql.close();

}

catch(Exception e)

{

e.printStackTrace();

}

return model;

}

//添加新闻

public int addNews(newsModel model)

{

int i=0;

String new_title=model.getTitle();

int new_type=model.getType();

int new_hints=model.getHints();

String new_source=model.getSource();

SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

Date new_time=model.getTime();

String newtime = sdf.format(new_time);

String new_content=model.getContent();

String new_author=model.getAuthor();

int new_sort=model.getSort();

try

{

sql=mydata.conn().createStatement();

//i=sql.executeUpdate("update j_new set title= '"+new_title+"',type='"+new_type+"',hints='"+new_hints+"',source='"+new_source+"',dateAndTime='"+new_time+"', content='"+new_content+"' where id="+new_id);

i=sql.executeUpdate("insert into j_new (title, type, hints,source,content,dateAndTime,author,sort) values ('"+new_title+"','"+new_type+"','"+new_hints+"','"+new_source+"','"+new_content+"','"+newtime+"','"+new_author+"','"+new_sort+"')");

sql.close();

return i;

}

catch(Exception e)

{

e.printStackTrace();

return i;

}

}

//修改新闻

public int updateNews(newsModel model)

{

int i;

int new_id=model.getId();

String new_title=model.getTitle();

int new_type=model.getType();

int new_hints=model.getHints();

String new_source=model.getSource();

SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

Date new_time=model.getTime();

String newtime = sdf.format(new_time);

String new_content=model.getContent();

String new_author=model.getAuthor();

int new_sort=model.getSort();

try

{

sql=mydata.conn().createStatement();

//i=sql.executeUpdate("update j_new set title= '"+new_title+"',type='"+new_type+"',hints='"+new_hints+"',source='"+new_source+"',dateAndTime='"+new_time+"', content='"+new_content+"' where id="+new_id);

i=sql.executeUpdate("update j_new set title= '"+new_title+"' , type='"+new_type+"', hints='"+new_hints+"' ,source='"+new_source+"' ,content='"+new_content+"' ,dateAndTime='"+newtime+"',author='"+new_author+"',sort='"+new_sort+"' where id="+new_id);

sql.close();

return i;

}

catch(Exception e)

{

e.printStackTrace();

return 0;

}

}

public int deleteNews(int id)

{

int i=0;

try

{

sql=mydata.conn().createStatement();

i=sql.executeUpdate("delete from j_new where id="+id);

}

catch(Exception e)

{

e.printStackTrace();

i=0;

}

return i;

}

}